Questions tagged [parameter]
The parameter tag has no usage guidance.
1
vote
1answer
40 views
Forced Parameterization and CASE statements
We have an application which uses an ORM which does not (easily) support parameterized queries. To combat this, I have enabled Forced Parameterization on our database (SQL2008 R2) in order to avoid ...
1
vote
1answer
59 views
ORA-48108 / ORA-48140 / ORA-48187 - diagnostic_dest value error(s) - init files / control files
This is all testing / training - none of this is related to a production env
Using ORACLE DB 12c + UNIX/LINUX 64-bit on an VM.
.
Hi Guys!
I am running in the below error(s) and seem unable to ...
0
votes
0answers
12 views
How does query parameters work under the hood?
I thought about how can it work under the hood and I came with an idea
Db engine receives query with parameters and also defined values of those parameters
SQL is being parsed into syntax tree like ...
1
vote
1answer
58 views
alternatives to passing temp table as a parameter to a stored procedure
some time ago I was developing a simple procedure to get the database sizes on a particular server. Here it is the procedure:
USE MASTER
GO
ALTER PROCEDURE dbo.sp_getDBSpace
@Radhe sysname=null,
...
5
votes
1answer
322 views
Unfamiliar syntax - Query with Parameters in Braces at the beggining
I have run sp_WhoIsActive on one of our servers using the following syntax:
sp_whoisactive @get_plans = 1, @show_sleeping_spids = 0, @get_outer_command = 1, @get_locks = 1
and found a spid with the ...
1
vote
1answer
113 views
How to pass an array to a plpgsql function with VARIADIC parameter
I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array.
I have already defined the function
CREATE OR REPLACE FUNCTION mix_table_fields(...
2
votes
1answer
33 views
Why are there multiple plans for query under forced parameterization?
I see the following query in an Azure SQL Database (that has Forced Parameterization = FORCED, Parameter Sniffing = ON, and Query Optimizer Fixes = ON) from within Query Store:
delete from [...
3
votes
1answer
60 views
How to pass an array of arguments to User Defined Aggregate in SQL Server
I have a custom aggregation (UDA) and I want to pass an array of arguments like so:
select dbo.MyAggregation(
t.type,
N'My aggregation property',
t.param1, t.param2, t.param3
)
from ...
0
votes
1answer
82 views
Enable or disable a task based on parameter
I have an SSIS package called Main_Flow that executes several other packages in order.
What I'd like to do is to somehow parametrise the enabling/disabling of packages in the main flow. This way I ...
2
votes
1answer
22 views
Is there a way to change the compaction throughput via startup options in Cassandra?
We are currently running a Cassandra cluster in K8S containers as Stateful sets. We're considering exposing the config file but we would like to know if perhaps we can provide this value as a startup ...
2
votes
1answer
302 views
Parameterized query creating many plans
I have some queries that are parameterized, but they are still creating a new execution plan each time. I am using SQL Server 2016.
Queries are like:
(@P1 varchar(1043),@P2 varchar(6))
UPDATE table
...
0
votes
2answers
122 views
SSRS - select where parameter and fixed value
So there is a lot of stuff about using SSRS parameters to select multiple values but nothing about using both a parameter and a fixed value using the in select statement.
So what I'm trying to do is ...
3
votes
1answer
1k views
“column … does not exist” error when calling an SQL procedure (new in PostgreSQL 11)
Using PostgreSQL 11 Beta 2, a very simple trading_holiday table is created in the config schema:
DROP TABLE IF EXISTS config.trading_holiday;
CREATE TABLE config.trading_holiday
(
id smallint ...
3
votes
1answer
166 views
Can't find compiled parameters values in the plan cache
I have found, through the query store, a query that's performing on average 297582 logical reads.
I wanted to see if I was able to tune that query a bit, and after that, try to execute the query ...
3
votes
2answers
638 views
Passing value of datatype interval in parametrized query
The context is connecting to a Postgres db from a rest server.
To consider a hypothetical representative example: I would like to be able to get a list of names where the account creation date is ...
0
votes
0answers
42 views
In-line TVF is using indexed views in a UNION - parameter should eliminate most of them, except it doesn't
I am using SQL Server 2016-SP1-CU8
Here is the code to setup my scenario:
--1. Drop indexed views (in case of re-running
IF OBJECT_ID('Widgets_1', 'V') IS NOT NULL DROP VIEW Widgets_1
IF ...
0
votes
1answer
19 views
Parameter evaluation against harcoded string always return false
In Mysql workbench I am trying to make query whit parameter.
But I have strange behavior when I try to compare parameter with static strings.
Why I do not get any rows form query like this ?
When I ...
2
votes
1answer
176 views
Postgres “session variables” kludge on AWS RDS
I am trying to migrate a PostgreSQL database to AWS RDS.
The problem I ran into was that the database in question makes extensive use of the SET kludge.variable TO 'foo' hack, that is, it uses the ...
0
votes
1answer
367 views
SQL SERVER dynamic Query with Like Statement for null values
I'm trying to execute a SQL dynamic query with nullable parameters using LIKE. If the parameter has a value, the query is successfully executed. But if parameter is NULL, query breaks returning only "...
3
votes
1answer
66 views
How to get the function name of a regprocedure in PostgreSQL?
I'm trying to write a function to get the name of the function associated with a given regprocedure variable, as follows:
CREATE OR REPLACE FUNCTION get_funcname(_fn regprocedure)
RETURNS text AS
$...
53
votes
2answers
4k views
How to create Unicode parameter and variable names
All of this works:
CREATE DATABASE [¯\_(ツ)_/¯];
GO
USE [¯\_(ツ)_/¯];
GO
CREATE SCHEMA [¯\_(ツ)_/¯];
GO
CREATE TABLE [¯\_(ツ)_/¯].[¯\_(ツ)_/¯]([¯\_(ツ)_/¯] NVARCHAR(20));
GO
CREATE UNIQUE CLUSTERED INDEX [¯...
0
votes
0answers
41 views
Oracle sysasm user unable to write to spfile
I have an Oracle RAC 12.2 cluster with the spfile stored in ASM. I cannot write to this spfile using the ALTER SYSTEM command.
I was able to modify some parameters by setting the scope to MEMORY, ...
2
votes
3answers
763 views
AddWithValue performance and plan cache implications
Recently I noticed my company's application using AddWithValueto pass parameter values to dynamic, parameterized queries. Example:
cmd.Parameters.AddWithValue("@VehicleId", Vehicles.VehicleId);
In ...
1
vote
1answer
1k views
How to create SQL Stored Procedure with parameters of type List<string>
I need to create a Stored Procedure that will take parameters of type List. For example :
Select *
From Table
Where State in @StateList
or Lob in @LobList
or Company in @CompanyList
The ...
0
votes
0answers
106 views
My most recent expensive query is sp_procedure_param_rowset
I'm stress testing our software running many transactions and reports at the same time on one same DB. analyzing the most recent expensive queries, I found that the top query is the ...
2
votes
0answers
162 views
What SSIS variable/ paramter to use to capture looping server name?
I am a newbie to SSIS.
BACKGROUND:- We are trying to make Database Inventory of about 100 SQL Instances. First task is to check SQL connectivity for all servers and capture the failed connections. ...
0
votes
0answers
86 views
See parameters passed to query in DB2 LUW 10.5 (AIX or Windows)
DB2 LUW 10.5 (AIX or Windows)
We have an application that we think might be passing incorrect parameters.
The obvious answer is to debug the application or turn on jdbc tracing, but I wanted to know ...
2
votes
2answers
95 views
Select roads that are between FROM and TO
I have a road table:
create table road
(road_id int,
name_ nvarchar2(15),
from_ nvarchar2(15),
to_ nvarchar2(15),
sort int
);
insert into road (road_id,name_,from_,to_,sort)...
1
vote
1answer
58 views
Function taking a string as table and column name
I'm trying to write a function that will create a new column in a participation table based on the presence of a id in survey_name table:
CREATE OR REPLACE FUNCTION survey_participated(_sn text)
...
4
votes
1answer
183 views
Proper use of varbinary type in MS SQL Server
I am at a new position and I am being told to implement a stored procedure that will accept a list of user ids and update a flag.
When I suggested the use of a table value parameter (array emulation),...
3
votes
3answers
6k views
Views with parameters, is it possible?
I am working in a PHP application and some "complex" queries are starting to appear in the code. Because of the complexity I am not able to use any ORM and the only resource I have is a plain SQL and ...
6
votes
1answer
2k views
Output parameter not set if stored procedure fails inside a TRY / CATCH
In SQL Server 2008 (but also in 2014). Let's consider a procedure that has an output parameter. This procedure may produce an error (and will in the following example). I note that the behaviour of ...
0
votes
2answers
325 views
Programatically scan a stored procedures parameters
I have been trying to figure out how to scan a stored procs parameters from the execution plan. I often see most people suggest using the ParameterList within the execution plan xml. This works fine ...
1
vote
1answer
3k views
PostgreSQL function param json_populate_recordset ARRAY problem
Previous question: PostgreSQL function parameters JSON to TYPE
I am trying to create a sample of code using json_populate_recordset, but it seems I am doing something wrong. I have created a test_GPS ...
0
votes
1answer
6k views
How can I pass parameter to stored procedure through a query? [duplicate]
I have this stored procedure:
CREATE PROCEDURE `test`(OUT `vote_value` INT, OUT `score` INT, IN `user_id` INT, IN `time_range` INT)
NO SQL
BEGIN
SELECT coalesce(sum(vote_value), 0), coalesce(...
1
vote
1answer
75 views
Should you OPTIMIZE FOR default values in a dynamic search query?
I have a dynamic search query that looks like this:
@CustID INTEGER = -2,
@Name NVARCHAR = ''
--more parameters with default values...
Select x, y, z FROM ...
0
votes
1answer
736 views
SQL - Passing a Stored Procedure Variable as a Parameter in a crystal report for end users to select
OK I know there are hundreds of posts out there that state how to pass a parameter from a stored procedure to a crystal report. But unfortunately with some research I haven't come across a specific ...
-1
votes
1answer
54 views
varray size can be a variable?
I need to create a varray, and its size must be a parameter of the procedure, for example
create or replace procedure x (cant number)
as
type arreglo is varray(cant) of number;
elarreglo arreglo :=...
1
vote
1answer
291 views
SQL Server 2012+ Agent Job with Parameter to determine job flow
Is it possible to have an Agent Job with an input parameter?
What I'm looking to do is have a flag to determine how the job flow will occur.
For example: I have a 10 step job. Steps 1-5 are always ...
1
vote
1answer
97 views
is it recommend to set “--setParameter ttlMonitorEnabled=false” for better performance if you don't use TTL Indexes
We run a few sharded clusters and many replicasets. Our customers don't use TTL Indexes.
Will there be a better performance if we disable background thread that is responsible for deleting documents ...
3
votes
2answers
1k views
Assign the result of current_setting('the_setting') to a variable in PL/pgSQL
I have a function where I need to get a configuration parameter and assign it to a variable that I will use later in the function. The problem is that when the configuration parameter is not ...
1
vote
2answers
750 views
Insert values from a record variable into a subclass table
My goal is to create a stored procedure that builds up a _tbl_out which is a subclass of a _tbl_in. I'm following the results of this question, but for this to work, the tables need to have the SAME ...
0
votes
1answer
3k views
INSERT INTO statement with parameters for tableName, columns and values?
Is it possible to have an SP that looks something like this:
DECLARE @tableName = null;
DECLARE @cols = null;
DECLARE @values = null;
INSERT INTO @tableName @cols @values;
The resultant query looks ...
5
votes
1answer
779 views
Odd behavior with SSRS 2016 drop down parameter list
I have a report that is "working" fine except for an issue.
I have four dropdown list parameters where I am getting the values from queries. When I select a value from a dropdown list, it selects ...
5
votes
1answer
13k views
How to use function parameters in dynamic SQL with EXECUTE?
I have written a PL/pgSQL function in PostgreSQL 9.5. It compiles well but when I call it from pgAdmin3 it gives me an error. It seems like the dynamic query with columns to be replaced with the ...
1
vote
2answers
134 views
SQL - Restricted view for transient (stateless) user
I’ve been looking for a solution to this for a few days now, but found nothing concrete. I’m not a DBA, so forgive me if this is a trivial question. The short of it is: I need to create a restricted ...
7
votes
1answer
2k views
How to use an array as argument to a VARIADIC function in PostgreSQL?
I am trying to make a case-insensitive version of json_extract_path_text(), using the citext module.
I would like this to be a simple wrapper around the built-in function, the only difference being ...
0
votes
1answer
40 views
SqlServer Passing parameters within Nesting procedures
I have these 2 procedures:
Create procedure jolly (@job_par varchar(100)='p%') as
select * from emp where job like @job_par
create procedure test1 @proc_name varchar(30)
as exec @proc_name
if i ...
0
votes
1answer
877 views
Importing Data in Parallel in SQL Server
I have more than 100 files to import into the sql server and most of them are of 500 MB. I want to leverage the the parallel import utility of SQL server and have read a number of webpages, like the ...
13
votes
1answer
214 views
What allows SQL Server to trade an object name for a string passed to a system procedure
What causes it to be legal to pass an object name to the system stored procedure sp_helptext?
What mechanism converts the object name to a string?
e.g.
-- works
sp_helptext myproc
sp_helptext [...
