I have a stored procedure, say "Test" with an optional parameter @Param1 int = NULL. In the procedure, the value of @Param1 is used to update the value of some column in some table, if the caller provided a value. If the parameter is not provided, the column is not updated. Unfortunately, that column allows NULLs, so that the caller isn't able to set the column value to NULL. So, the question is: Is the procedure able to distinguish between the following two calls? `EXEC Test` -- intended meaning: don't update the column `EXEC Test @Param1 = NULL` -- intended meaning: set the column to `NULL` Of course, the procedure can check if `@Param1 IS NULL`. But can it determine if the parameter has been provided at all?

I am trying to create a function which will dynamically create a unique table name, populate the table with a result set returned from another function, and return the table name to the caller. In outline, it looks something like this: ``` CREATE OR REPLACE FUNCTION return_result_table( _param1 integer[], _param2 text[]) RETURNS text LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE _table_name TEXT; _select TEXT; BEGIN _table_name := '_XYZABC_' || replace(current_date::text,'-','_') || '_' || 'ZXCVBN'; -- not the real code _select := '(select * from some_other_function(_param1, _param2))'; execute 'create table some_schema.' || _table_name || ' as ' || _select; return _table_name; END; $BODY$; ``` The code is accepted, and it appears to be forming the command correctly as ``` execute 'create table some_schema._XYZABC_2020_10_07_ZXCVBN as (select * from some_other_function(_param1, _param2))' ``` but when I try to execute it with: ``` SELECT return_result_table(ARRAY[0,1,5,19],ARRAY['AA,'BB','CC']) ``` I get the error ERROR: column "_param1" does not exist LINE 1: ...ect * from return_result_table(_param1... It appears that the code is trying to substitute in the value of a column (of what?) rather than using the parameter. How can I prevent this translation happening, so that I can pass the parameters through into the called function?

I use rpc_completed event in order to capture executed sprocs. I am also to view the statement and passed parameters by using the following way: right-click extended event -> Watch Live Data and finally see statement+parameters However I want to see the captured data in table format, but I wasn't able to implement it. When I run below query it shows statement without parameters. How can I see parameters in the table form? Need your suggestions. ``` IF OBJECT_ID('tempdb..#capture_waits_data') IS NOT NULL DROP TABLE #capture_waits_data SELECT CAST(target_data as xml) AS targetdata INTO #capture_waits_data FROM sys.dm_xe_session_targets xet JOIN sys.dm_xe_sessions xes ON xes.address = xet.event_session_address WHERE xes.name = 'LongRunningQueries' AND xet.target_name = 'ring_buffer'; --*/ /**********************************************************/ SELECT CONVERT(datetime2,SWITCHOFFSET(CONVERT(datetimeoffset,xed.event_data.value('(@timestamp)[1]', 'datetime2')),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS datetime_local, xed.event_data.value('(@name)[1]', 'varchar(50)') AS event_type, xed.event_data.value('(data[@name="statement"]/value)[1]', 'varchar(max)') AS statement, xed.event_data.value('(data[@name="duration"]/value)[1]', 'bigint')/1000 AS duration_ms, xed.event_data.value('(data[@name="physical_reads"]/value)[1]', 'bigint') AS physical_reads, xed.event_data.value('(data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads FROM #capture_waits_data CROSS APPLY targetdata.nodes('//RingBufferTarget/event') AS xed (event_data) WHERE 1=1 and xed.event_data.value('(action[@name="username"]/value)[1]', 'varchar(255)') <> 'NT SERVICE\SQLSERVERAGENT' ``` Here is actual used extended event: ``` CREATE EVENT SESSION [LongRunningQueries] ON SERVER ADD EVENT sqlserver.rpc_completed( ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.username) WHERE ([duration]>(

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 sp_executesql @stmt = N'SELECT TOP(@n) a, b FROM (VALUES(1,2)) v(a,b)', @params = N'@n int', @n = 10 ``` MAXDOP cannot: ``` exec sp_executesql @stmt = N'SELECT TOP(10) a, b FROM (VALUES(1,2)) v(a,b) OPTION (MAXDOP @n)', @params = N'@n int', @n = 10 ``` Msg 102, Level 15, State 1, Line 8 Incorrect syntax near '@n'. I suppose there are many more examples of what works and what doesn't. I'm looking for an authoritative list to eliminate trial and error.

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, 260915601, 260929877, 260939625, 260939946, 261096977, 261147037, 261152934, 261163936, 261357728, 261369122, 261376714, 261454472, 261488500, 261527284, 261584786, 261619749, 261679560, 261777653, 261786639, 261795246, 261795810, 261803724, 261821199, 261824173, 261827397, 261840197, 261848595, 261874545, 261889122, 261889355, 261929793, 261953069, 262106609, 262134069, 262134088, 262339745, 262354363, 262360015, 262571936, 262586920, 262591486, 262663776, 262703601, 262746674, 262792439, 262801544, 262826561, 262933229, 262933270, 262947539, 262958110, 263021588, 263032875, 263037208, 263039292, 263045038, 263085369, 263089147, 263091427, 263097644, 263100021, 263103339, 263104396, 263956373) ``` If I check the execution plan it uses the primary key, but it executes 65 times and add a Constand Scan and Nested Loops operation item. However - if I reduce the number of parameters to 64, then it executes only 1 time directly with no other operations. I can see that with 65+ parameters the Seek Predicates only contain one element, and if the number of parameters is 64 or less the Seek Predicates contains all of the elements directly. Is it possible to avoid MSSQL to execute as many times a parameters when number of parameters is more than 64? On small tables the difference is not that big, but if I join the results with other tables the numbers of reads difference becomes huge. To reproduce this with the StackOverflow2013 database, for example: ``` /* 63 rows: */ SELECT * FROM dbo.Users WHERE Id IN (-1,1,2,3,4,5,8,9,10,11,13,16,17,19,20,22,23,24,25,26,27,29,30,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,48,49,50,51,52,55,56,57,58,59,60,61,62,63,64,67,68,70,71,72,73,75,76,77,78); /* 64 rows: */ SELECT * FROM dbo.Users WHERE Id IN (-1,1,2,3,4,5,8,9,10,11,13,1

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". If I manually call them as they appear in the trace's TextData, they generate an exception - but no exceptions are being generated in the original trace. The Error column for these "parameterless" calls does show "1 - Error," but I don't understand how the call is not generating an exception. At first I suspected it was a glitch with profiler, that "1 - Error" caused parameters to be truncated from the TextData, but I see the same results in an extended events session. Has anyone seen anything like this in their profiler traces or event sessions? The app is almost certainly making these calls as part of some edge-case error, but why is sql server not throwing an exception?

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 sql_command (the column shown when `@get_outer_command` is set to 1) as follows ``` (@p1 int,@p2 int) Exec MyDatabase.MyProc @p1 @p2 ``` When I try to run a query with this syntax on my test Adventureworks database: ``` (@be int) SELECT * FROM Person.Person WHERE BusinessEntityID = @be ``` I get the error Msg 1050, Level 15, State 1, Line 1 This syntax is only allowed for parameterized queries. Msg 137, Level 15, State 2, Line 4 Must declare the scalar variable "@FN". so it seems to be something to do with parameterized queries. This makes sense as the variable @be is never set to a value What is happening here?

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 [CMS_WebFarmTask] where [TaskIsAnonymous] = @0 and [TaskID] not in ( select [TaskID] from [CMS_WebFarmServerTask] ) and [TaskCreated] < dateadd ( minute , @1 , getdate ( ) ) ``` It has a large number of plans (17 in the last hour), despite both parameters being integers with identical values in each plan: ``` ``` Why isn't only one plan being used?

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 someTable t group by t.type ``` The above is just an example, and I want to pass arbitrary number of params (i.e 4-...). I've tried to use `SqlString` as an "array" with some delimiter, and then split the string by this delimiter, but this doesn't seem like a good solution.

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 SET FILEDATA=@P1 WHERE FILEID=@P2 ``` This query is not using the already generated execution plan from the cache, rather it is creating a new plan each time.