Handling optional parameters in stored procedure

Question

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?

• Andy: Actually I'm looking for a generic solution that would work for all datatypes. Think bit as an example where it is not possible to have a well-known value the caller would never use.

Answer 1

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
 CROSS APPLY sys.dm_exec_input_buffer(ER.session_id, ER.request_id) AS IB
 WHERE ER.session_id = @@SPID
 AND IB.event_info LIKE '%@Param1%'
 )
 BEGIN
 IF @Param1 IS NULL
 BEGIN
 RAISERROR('@Param1 was supplied as NULL', 0, 1) WITH NOWAIT;
 END;
 ELSE
 BEGIN
 RAISERROR('@Param1 was supplied as a non-NULL value', 0, 1) WITH NOWAIT;
 END;
 END;
 ELSE
 BEGIN
 RAISERROR('@Param1 was not supplied and defaulted to NULL', 0, 1) WITH NOWAIT;
 END;
END;
GO

Test:-

EXEC dbo.Test @Param1 = NULL;
GO
EXEC dbo.Test @Param1 = 123;
GO
EXEC dbo.Test;
GO

Results (on 15.0.4102.2):-

@Param1 was supplied as NULL
@Param1 was supplied as a non-NULL value
@Param1 was not supplied and defaulted to NULL

---

@@SPID and CURRENT_REQUEST_ID can be used to provide the parameters to sys.dm_exec_input_buffer. No special permissions are needed to use that DMV for the current session. More rights are needed to view details for other sessions.

• Peter Vandivier: Allowing a low-trust service account appropriate access to a restricted proc is covered elsewhere on this network and externally. As a proof of concept addressing the question as asked, this is the correct answer and should be accepted.

Answer 2

ANSWER: There is no way of telling if parameter in called procedure was set to it's default value by user or by system.

The value inside procedure looks exactly same and there is no information stored in the system if the value was passed by calling script or default value was assigned by SQL server.

WORKAROUND: You can use second optional parameter like @set_null bit = 0 and set NULL to column only when @Param1 IS NOT NULL OR @set_null = 1.

ALTER PROCEDURE Test
 @Param1 VARCHAR(10) = NULL,
 @set_null BIT = 0
AS
BEGIN
 IF @Param1 IS NOT NULL OR @set_null = 1
 UPDATE my_table
 SET name = @Param1;
END;

Answer 3

As a workaround approach: if you can modify the caller code to supply a document instead, you can both

1. detect if a key/column exists in the supplied document to omit updates where a key is not present and
2. allow for set-null commands to valid keys

Consider the following (slightly bulky) POC (full db<>fiddle)

create proc Test 
 @payload nvarchar(255),
 @echo bit = 1,
 @execute bit = 0
as
begin
 declare 
 @sql nvarchar(1000) = N'update dbo.foo set '+nchar(10),
 @params_schema nvarchar(1000) = N'';
 declare 
 @a int,
 @b varchar(10),
 @c varchar(10);
 drop table if exists #col_cache;
 select 
 c.column_id,
 c.[name],
 p.[key],
 p.[value],
 p.[type] as j_type,
 t.[name] as data_type,
 t.max_length,
 t.precision,
 t.scale,
 case
 when t.[name] in (
 N'time',
 N'datetime2',
 N'datetimeoffset',
 N'smalldatetime',
 N'datetime',
 N'varbinary',
 N'varchar',
 N'binary',
 N'char',
 N'nvarchar',
 N'nchar'
 )
 then N'('+replace(convert(nvarchar(10),c.max_length),N'-1',N'MAX')+ N')'
 when t.[name] in (N'decimal',N'numeric')
 then N'('+convert(nvarchar(10),c.precision)+N','+convert(nvarchar(10),c.scale)+N')'
 else
 N''
 end as type_suffix
 into #col_cache
 from sys.columns c
 outer apply (
 select 
 j.[key],
 j.[value],
 j.[type]
 from openjson(@payload, '$') as j
 where j.[key] = c.[name] collate database_default
 ) as p
 left join sys.types as t on t.user_type_id = c.user_type_id
 where c.[object_id] = object_id(N'dbo.foo') 
 and c.is_identity = 0;
 with kvp as (
 select 
 [key],
 [value]
 from #col_cache
 )
 select 
 @a = a,
 @b = b,
 @c = c
 from kvp
 pivot (
 max([value])
 for [key] in (a,b,c)
 ) p;
 
 select 
 @sql += string_agg(
 N' '
 + quotename(cc.[name]) 
 + N' = '
 + iif(
 cc.[key] is null, 
 quotename(cc.[name]),
 N'@_' + cc.[name]
 ), 
 nchar(44) + nchar(10)
 ),
 @params_schema += string_agg(
 N'@_' 
 + cc.[name]
 + N' '
 + cc.[data_type]
 + cc.type_suffix,
 nchar(44)
 )
 from #col_cache as cc;
 
 select @sql += nchar(10) + N'where a = @_a;';
 if @echo = 1
 begin
 select 
 @sql as [sql],
 @params_schema as params_schema;
 end
 if @execute = 1
 begin
 exec sp_executesql 
 @sql,
 @params_schema,
 @a,@b,@c;
 end
end

Note the above pattern is limited to a single table pattern of the form...

create table foo (
 a int not null primary key,
 b varchar(10),
 c varchar(10)
);

...and requires modification for each table definition against which it might be applied (unless you fancy nesting dynamic SQL (which I don't)). The approach is similar to the catch-all where clause solution described in greater detail by Erland Sommarskog and Aaron Bertrand.

By way of local example. If our table dbo.foo had the following, data...

a	b	c
1	init	init
2	init	init

...an execution of the following commands...

exec Test N'{"a":1,"b":"foo","c":null}', @execute = 1;
exec Test N'{"a":2,"b":"bar"}', @execute = 1;

...would result in the following data...

a	b	c
1	foo	NULL
2	bar	init

Answer 4

As others have said, there is no good way to detect if a parameter was set to its default value by the caller or by its omission.

To add an extra option to the list of workarounds, you could make the default instead something that will never get used and react differently to that. For instance:

CREATE PROCEDURE DefaultJiggeryPokery @InputParam INT = -2147483648
AS BEGIN
 IF @InputParam = -2147483648 BEGIN 
 /* anything you need to do in response to the param being missing */
 END ELSE BEGIN
 /* anything you need to do in response to the param being explicitly set */
 END
END

This will limit the range of valid values for the parameter from "-2,147,483,648..2,147,483,647 plus NULL" to "-2,147,483,647..2,147,483,647 plus NULL" by ascribing a special meaning to INT_MIN. For string parameters you could use a long random value (some UUIDs concatenated?).

Be careful with this any most of the other workarounds though, it can lead to performance issues due to bad optimisation, through parameter sniffing issues and other hard to diagnose problems. You may be better off having specific procedures for the different possible actions (set value, set NULL, ...).

Answer 5

I would go for a universal placeholder in this case Exec Test @param1 then let the code snippet that calls this stored procedure to determine whether the @param1 value is set to NULL or left as an empty string given the scenario at hand.