Skip to main content
Share Your Experience: Take the 2024 Developer Survey
44 votes
Accepted

How to create Unicode parameter and variable names

Well, identifiers are always Unicode / NVARCHAR, so technically you can't create anything that doesn't have a Unicode name 🙃. The problem you are having here is due entirely to the ...
Solomon Rutzky's user avatar
22 votes

How to create Unicode parameter and variable names

I don't think it's Unicode that's causing the problem; in the case of local variable or parameter names, it's that the character isn't a valid ASCII/Unicode 3.2 character (and there isn't any escaping ...
Aaron Bertrand's user avatar
18 votes
Accepted

How to use function parameters in dynamic SQL with EXECUTE?

You are confusing a couple of things. To pass values to EXECUTE, use the USING clause. You don't need format() here. CREATE OR REPLACE FUNCTION insert_records_for_notification( _username text ...
Erwin Brandstetter's user avatar
16 votes
Accepted

Better execution plan if parameter is stored in local variable

Using local variables prevents sniffing of parameter values, so queries are compiled based on average distribution statistics. This was the workaround for some types of parameter sensitivity problem ...
Paul White's user avatar
  • 85.7k
13 votes
Accepted

How to use an array as argument to a VARIADIC function in PostgreSQL?

Pass the array as is using the VARIADIC key word in the call: CREATE OR REPLACE FUNCTION json_extract_path_text(string citext, VARIADIC params text[]) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL ...
Erwin Brandstetter's user avatar
13 votes

Parameterized query creating many plans

The length of @P1 is different in all of those. You're getting different plans because you're not explicitly setting parameter lengths in your code. Any difference in strings, no matter how minor, ...
Erik Darling's user avatar
12 votes
Accepted

What allows SQL Server to trade an object name for a string passed to a system procedure

The first argument to the system stored procedure sp_helptext is: [@objname= ] 'name' Is the qualified or nonqualified name of a user-defined, schema-scoped object. Quotation marks are required ...
11 votes
Accepted

AddWithValue performance and plan cache implications

I strongly recommend always using the method that allows you to explicitly set the data type and precision. Yes, this is more work, both up front and if the data type in the database ever changes (...
Aaron Bertrand's user avatar
10 votes
Accepted

RETURN value directly from INSERT with RETURNING clause

You can simplify the whole function and use an OUT parameter, so the value is returned at the end of the function automatically: CREATE OR REPLACE FUNCTION make_date_from_configuration( ...
Erwin Brandstetter's user avatar
9 votes
Accepted

Proper use of varbinary type in MS SQL Server

I was told to implement as demonstrated below as this is how its been done before. ... this smells funny to me. You know what smells funnier than this approach? That line of "reasoning". The old "...
Solomon Rutzky's user avatar
8 votes
Accepted

Output parameter not set if stored procedure fails inside a TRY / CATCH

You're off to a good start with that test setup, but it is missing something that is causing you to misinterpret what is actually happening. If you put in PRINT statements at the beginning, middle, ...
Solomon Rutzky's user avatar
8 votes
Accepted

Passing value of datatype interval in parametrized query

TLDR: Skip to chapter "Superior query" below. You didn't disclose the module you are working with, but the problem is obviously one of type casting. Looks like your parameter is passed as ...
Erwin Brandstetter's user avatar
8 votes

SqlCommand using Parameters but still it is creating Multiple Plans for One Query

Do not use AddWithValue to add parameters to the collection. SqlClient will infer the SqlDbType based on the .NET type and define the parameter length for string types based on the actual value length ...
Dan Guzman's user avatar
  • 28.3k
7 votes
Accepted

Unfamiliar syntax - Query with Parameters in Braces at the beggining

You are right, the (@be int) shown applies to parameterized queries. Applications often parameterize the queries using sp_executesql, and then sent them to the sql server. The query will be cached ...
Randi Vertongen's user avatar
7 votes
Accepted

Mysterious procedure calls without parameters - but no exceptions generated

SET PARSEONLY ON will do this. FMTONLY will generate an error. EG: USE [AdventureWorks] GO set parseonly on go exec [uspGetBillOfMaterials] go set parseonly off
David Browne - Microsoft's user avatar
7 votes
Accepted

How to capture runtime parameters with extended event?

If the procedures are being called, you should absolutely see the parameter values in the 'statement' column. I have a blog post that demonstrates exactly this issue. There's literally nothing magical ...
Grant Fritchey's user avatar
6 votes

RETURN value directly from INSERT with RETURNING clause

Use RETURN QUERY RETURN QUERY ... Or if you're returning a single row, not in a RETURNS TABLE or RETURNS SETOF ... function, I think you can store the result into a record-valued variable and return ...
Craig Ringer's user avatar
  • 56.6k
6 votes

AddWithValue performance and plan cache implications

Does that conversion affect SQL Server performance in the case of an 'INT'? No. Some .NET data types map exactly to SQL Server data types, and int is one of them. And this method is not deprecated. ...
David Browne - Microsoft's user avatar
6 votes

sp_executesql: what can be parameterized

Background The reason why these sometimes don't work isn't specific to sp_executesql, but rather to how variables work in SQL Server. Your command wouldn't work if you used a local variable and no ...
LowlyDBA - John M's user avatar
5 votes

Assign the result of current_setting('the_setting') to a variable in PL/pgSQL

In Postgres 9.6 or later you can have this much simpler. Like a_horse commented, there is a new variant of current_setting() that takes a second parameter missing_ok, to suppress exceptions if the ...
Erwin Brandstetter's user avatar
5 votes
Accepted

SQL Stored Procedure with Output Parameter

Output parameters in stored procedures are useful for passing a value back to the calling T-SQL, which can then use that value for other things. Let's say you have a stored procedure that returns a ...
Randolph West's user avatar
5 votes
Accepted

Can't find compiled parameters values in the plan cache

sp_prepare doesn't include the compiled values for parameters, because it doesn't 'sniff' them. When a query is issued with it, cardinality estimates are made using the density vector rather than the ...
Erik Darling's user avatar
5 votes

More than 64 parameters to WHERE IN sentence makes MSSQL reading far too many rows

An IN clause gets "rewritten" to this form, essentially: WHERE TableID = 260905384 OR TableID = 260915601 OR TableID = 260929877 ... I have heard, and observed, that SQL Server has ...
Josh Darnell's user avatar
  • 29.4k
5 votes

Handling optional parameters in stored procedure

Maybe something like this might work:- CREATE PROC dbo.Test @Param1 INT = NULL AS BEGIN SET NOCOUNT ON; IF EXISTS ( SELECT 1 FROM sys.dm_exec_requests AS ER ...
Rob Dalzell's user avatar
4 votes

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

Postgres 14 adds SQL syntax addressing this problem. Now you can append an AS clause to declare a new table alias for the columns merged in the USING list: CREATE OR REPLACE FUNCTION f_merge_foobar() ...
Erwin Brandstetter's user avatar
4 votes

SQL Stored Procedure with Output Parameter

Assuming this question pertains to SQL Server: It comes down to context and efficiency. Context = App Code When executing the Stored Procedure from app code, it isn't much different in terms of the ...
Solomon Rutzky's user avatar
4 votes

PostgreSQL function param json_populate_recordset ARRAY problem

Confusion? From the docs json_populate_recordset(base anyelement, from_json json) does not return an ARRAY, it returns a setof anyelement. That's fundamentally different from an array. SELECT * FROM ...
Evan Carroll's user avatar
  • 63.5k
4 votes
Accepted

Select roads that are between FROM and TO

select * from road connect by (prior to_ = from_ and from_ != 'Street V') start with name_ = 'Street B' and from_ = 'Street II' ; ROAD_ID NAME_ FROM_ TO_ SORT ---------- ----...
Balazs Papp's user avatar
  • 40.7k
4 votes
Accepted

How to pass an array of arguments to User Defined Aggregate in SQL Server

The SQLCLR API does not allow for either overloading of methods nor the params keyword. To pass in a variable number of parameters, you have a few options: Concatenate them into a delimited string ...
Solomon Rutzky's user avatar
4 votes

How to pass an array to a plpgsql function with VARIADIC parameter

To pass an actual array for a VARIADIC parameter, you must use the keyword VARIADIC in the call: SELECT mix_table_fields('art'::VARCHAR , 'out'::VARCHAR , ...
Erwin Brandstetter's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible