Questions tagged [parameter]
The parameter tag has no usage guidance.
134
questions
4
votes
1answer
50 views
sp_executesql: what can be parameterized
Is there a definitive list published showing what can and cannot be parameterized in a call to sp_executesql.
For example, predicates can, table names cannot. Number of rows in TOP can:
exec ...
5
votes
2answers
88 views
More than 64 parameters to WHERE IN sentence makes MSSQL reading far too many rows
I have some queries where I need to get more than 64 specific rows, like this example with 65 IDs.
TableID is primary key, type BigInt.
SELECT * FROM TableA
WHERE TableID IN (260905384, ...
0
votes
1answer
18 views
Postgres: create function with a parameter, which will become the part of IN (1, 2, 3) clause
Following is the part of function declaration. Unfortunately it doesn't work the way I would like it to.
CREATE FUNCTION my_cool_func(some_parameter INTEGER, some_other_parameter INTEGER[])
RETURNS ...
0
votes
1answer
25 views
Retrieving the value of $1, $2 etc. from a Postgres Query
I am currently trying to update/modify a number of SQL queries on a system that does not have any internal Documentation (Developer left on short notice and on bad terms).
I have enabled Query ...
1
vote
2answers
47 views
Why does this stored proc cause a clustered index scan, but seek when using OPTION RECOMPILE?
I think I may know the answer based on my research, but am looking for confirmation on how/why the engine compiles the plan the way it does with
Parameters being passed in:
@ID int
,@OtherID INT
...
0
votes
0answers
11 views
Does replica config parameter affect primary/master RDS Postgres?
I want to add hot_standby_feedback = on to a RDS readonly replica.
The problem is that the replica currently has a parameter group already attached, which is inherited from the master. My question is:...
0
votes
2answers
600 views
SSRS report does not work with multiple parameters
My data set has the following where clause:
WHERE a11.PDPOID LIKE '%' + @POID + '%'
My parameter @POID properties are set to single value. The report works, but if I change the parameter properties ...
0
votes
2answers
135 views
Parameterized Dynamic TSQL - Dynamic Parameters
We have a situation where effectively the same report is requested by different customers but they:
Don't want all the columns
Want the columns in a different order than we naturally have them
Want ...
6
votes
1answer
486 views
Mysterious procedure calls without parameters - but no exceptions generated
I've been tracing a handful of stored procedures with EventClass RPC:Completed that are intermittently called from .Net SqlClient Data Provider without required parameters - just "exec SprocName".
...
2
votes
2answers
282 views
Stored Procedure - Reseed Identity
I have a bunch of LU tables that have values added and deleted all the time. Therefore, I figured writing a quick stored procedure would serve me well and then can call the stored procedure on another ...
0
votes
1answer
333 views
Mysql Command line Parameter passing to sql file
In windows mysql(5.7 version),
I want to run mysql commmand using command line for .sql file as input .
Also i was to pass some variable whose value should get used in .sql file.
somehow I am not able ...
1
vote
1answer
76 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 ...
2
votes
1answer
873 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
36 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
700 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,
...
6
votes
1answer
567 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 ...
2
votes
1answer
2k 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(...
3
votes
1answer
60 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
254 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 ...
1
vote
1answer
716 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
45 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 ...
3
votes
1answer
467 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
193 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
7k 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
248 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 ...
5
votes
2answers
3k 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
49 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
23 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 ...
3
votes
1answer
434 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
1k 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
241 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
5k 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 [ĀÆ...
3
votes
3answers
2k 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
4k 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
158 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
292 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. ...
2
votes
2answers
99 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
78 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
308 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
4answers
12k 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 ...
7
votes
1answer
4k 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
415 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
6k 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
9k 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
83 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
1k 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
72 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
669 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
174 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
3k 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 ...
