Questions tagged [parameter]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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 [¯...
33
votes
3answers
56k views

Table-Valued Parameter as Output parameter for stored procedure

Is it possibile to Table-Valued parameter be used as output param for stored procedure ? Here is, what I want to do in code /*First I create MY type */ CREATE TYPE typ_test AS TABLE ( id int ...
19
votes
2answers
8k views

Why must TVPs be READONLY, and why can't parameters of other types be READONLY

According to this blog parameters to a function or a stored procedure are essentially pass-by-value if they aren't OUTPUT parameters, and essentially treated as a safer version of pass-by-reference if ...
16
votes
1answer
4k views

Naming conflict between function parameter and result of JOIN with USING clause

Given this setup in current Postgres 9.4 (from this related question): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') ...
13
votes
1answer
229 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 [...
11
votes
1answer
62k views

PLS-00306 Error: How to find the wrong argument?

PLS-00306: wrong number or types of arguments in call to 'string' Cause: This error occurs when the named subprogram call cannot be matched to any declaration for that subprogram name. The ...
10
votes
1answer
658 views

Poor query performance

We have a large (10,000+ lines) procedure that typically runs in 0.5-6.0 seconds depending on how much data it has to work with. Over the past month or so it has started taking 30+ seconds after we do ...
7
votes
2answers
9k views

SQL Server passing variable to a string in a stored proc

I am trying to concatenate a custom string with a variable that is an int. Investigation is pending for ['+ @investigationidout +']. When I hover the mouse pointer over the first + sign it says: ...
7
votes
1answer
3k views

Better execution plan if parameter is stored in local variable

I have two stored procedures. This one is incredibly fast (~ 2 seconds) CREATE PROCEDURE [schema].[Test_fast] @week date AS BEGIN declare @myweek date = @week select distinct serial ...
7
votes
2answers
1k views

Parameter Sniffing: Why Does This Become An Issue?

Today, I had an issue with a stored procedure timing out (took longer than 30 seconds) when it was run from an ASP.NET web page, but executed quickly when run from SSMS (took 5 seconds). After ...
7
votes
1answer
3k 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 ...
7
votes
2answers
8k views

How to Determine the input and output values of stored procedures?

I have hundreds of SPs and I would like to find out: input parameters with type output fields with type (not output parameters) Of course I could manually go through each one and write it down but ...
7
votes
1answer
3k 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 ...
7
votes
2answers
13k views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...
6
votes
1answer
18k 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 ...
6
votes
3answers
23k views

SQL Stored Procedure with Output Parameter

I know how to write a stored procedure with output parameters. But I don't know the reason why I would use it over just using a simple SELECT statement. A normal stored procedure can still return an ...
6
votes
1answer
462 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". ...
6
votes
1answer
4k views

How bad is query plan pollution (too many equivalent plans in cache)?

Question currently affects C#/.NET, DB access based on ADO.NET and SQL Server 2008 R2, but I think it applies to other databases as well. I noticed some old modules of a system have non-optimal SQL ...
6
votes
1answer
5k views

RETURN TABLE is converted to RETURN SETOF int in Postgres function

In pgAdminIII I create a function that returns multiple rows in a single column named identifier using RETURN TABLE: CREATE OR REPLACE FUNCTION test( IN parm1 date, IN parm2 interval) ...
6
votes
1answer
165 views

Benefit or use-case for empty variable / parameter / temporary table / temporary procedure names?

I just discovered, through sheer brilliance accidentally, that SQL Server allows you to create variables, parameters, table variables, temporary tables (local and global), and temporary stored ...
5
votes
2answers
3k views

SQL Server: Do Statistics Updates cause query plans to be flushed? Should they?

I've found that a big, nasty data-extraction query that runs daily needs updated stats to avoid making horrible query plans based on incorrect rowcount estimates (let's not worry about whether or not ...
5
votes
2answers
17k views

How to remove all hidden parameters from Oracle Database

How do I remove all hidden parameters from Oracle Database before upgrading? I'm using the SQL query below. So what do you recommended? SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\...
5
votes
1answer
390 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 ...
5
votes
1answer
845 views

When should startup parameter -E be used?

We are starting to build a data warehouse system using SQL Server 2008 R2. One of the senior DBAs told us to consider using a startup parameter -E for the SQL Server service. I read from Books Online ...
5
votes
2answers
2k 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 ...
4
votes
1answer
230 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),...
4
votes
2answers
431 views

switch TOP clause between percentage and rows by parameter

For one report I am making query where users suppose have to choice TOP values based on percents or fix amount of rows. I have two ideas Calling two different sub stored procedure based on passed ...
4
votes
3answers
1k views

SQL Server - How to protect against SQL Injection when dealing with a Dynamic WHERE clause passed to a Stored Procedure

Given the following stored procedure, can I protect against SQL injection in the supplied additional WHERE condition? CREATE PROCEDURE [dbo].[ProtectDynamicWhereClause] (@TableName varchar(50), ...
4
votes
3answers
666 views

How can I safeguard against SQL injection where parameterizing my query is not an option?

I'm working with ESRI's arcpy module, and I need to specify a WHERE clause as a parameter to this function. Since the result will eventually be used to retrieve data from the database, the text of the ...
4
votes
1answer
960 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 ...
3
votes
3answers
1k 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 ...
3
votes
1answer
391 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 ...
3
votes
1answer
3k views

SQL Parameter Sniffing Possible Workaround

Our system has roughly 500 "Customers" that have extreme differences in record counts. Here is a (greatly simplfied) sample query that could return 0 - 100000 rows depending on the parameter passed. ...
3
votes
2answers
2k views

SET ROLE via parameterized query

In the very helpful response I received to a previous question, I am trying to write some JDBC code that first sets the role to a specific user before executing subsequent queries. On the safe side I ...
3
votes
1answer
206 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
2k 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 ...
3
votes
2answers
1k views

What's row size in mysql?

What does row size mean in mysql? I have a mysql db and table in myisam and it shows Space usage Type Usage Data 1.3 GiB Index 429.1 MiB Total 1.7 GiB Row Statistics Statements ...
3
votes
1answer
124 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 ...
3
votes
4answers
9k 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 ...
3
votes
2answers
23k views

Pass parameters to a procedure with SQLcmd

I use this SQL command: sqlcmd -v first_date="30/09/2015" last_date="15/09/2016" And I would like to pass that parameters to a stored procedure: ALTER PROCEDURE [dbo].[_testProcedure] WITH ...
3
votes
1answer
3k 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
122 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 $...
3
votes
1answer
47 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
2k views

Logging Stored Procedure Parameter Values

I've worked in an SQL Server environment before where logging was made part of our stored procedures to capture execution start/end, parameter values and error messages which I found very useful and ...
3
votes
1answer
685 views

Update Table Query: Automatically handle “Enter Parameter Value” prompts?

EDIT: Here are two database Files that demonstrate an applied example of the problem: Example_DBs.zip (Zipped due to 2 link limitation.) Note: The missing Field in "Not Working.mdb" is intentional....
3
votes
1answer
937 views

Package parameter blank in SSIS catalog

I have deployed a package with 2 package parameters which had their values set inside the package prior to deployment. However if I look at those parameters in my SSIS catalog project the values of ...
2
votes
3answers
17k views

How can I pass parameters to an “SQL Script” from isql

I am wondering if it is possible to pass parameters to an SQL script from within sybase's isql utility. For example, I'd like to store a select statement in the file the_script.sql that would look ...
2
votes
2answers
76 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 ...
2
votes
1answer
543 views

Best Practice for Passing and Working With Parameters Security/Performance

Our senior programmer has been having me write stored procedures in the following format to protect against injection attacks. He says that the best practice is to take the parameter in, then declare ...
2
votes
2answers
5k views

How to pass parameter in select query(In with clause) and not in where condition in SQl Server SSIS Package?

I want to pass a parameter (@date1) in my SSIS OLE DB Source so I created a variable and I tried to pass the parameter using '?' but it's showing 'Syntax error, permission violation or other non-...