How to create stored procedure that accepts optional parameter in SQL Server?
To create optional parameter in stored procedure, we set the parameter value to NULL while creating a stored procedure.
CREATE PROCEDURE [dbo].[GetAllPersonalDetails] @personalDetailsId int = null AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here IF @personalDetailsId is null BEGIN SELECT * FROM PersonalDetails END ELSE BEGIN SELECT * FROM PersonalDetails WHERE PersonalDetailsId = @personalDetailsId END ENDThe above stored procedure may or may not be passed @personalDetailsId parameter value and it can be called in any of the following way
-- Without parameter EXEC GetAllPersonalDetails -- With parameter EXEC GetAllPersonalDetails 1Related Posts
I want to modify a SP in MySQL, but don't want it to crash apps currently using that SP.
The SP is: SP_CreateUserReport
Argument 1: Check4ItCode
Argument 2: PasscodeAdmin
Currently, the calling app sends something like:
Call SP_CreateUserReport('CfiCode','12324')
But I want to add a new argument to the SP:
Argument 3: DemoUserEmail
When I do that, and do the same call, the SP will not receive the 3rd new argument and fail.
Eventually, I will update the all the calls from various apps to add the 3rd argument value, But in the meantime, I want to make that 3rd argument optional till I can update all the calling apps. And, if that value is not there, store a NULL in the table for that field.
Here is a sample of the final code I wish to use.
DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_CreateUserReport`( Check4ItCode varchar(255) , PasscodeAdmin varchar(255), DemoUserEmail varchar(255) ) BEGIN INSERT INTO tbl_000_010_MAIN_REPORT_INFO ( strCFICode, strPasscodeAdmin, strDemoUserEmail) SELECT Check4ItCode as CFICode, PasscodeAdmin AS AdminCode, DemoUserEmail as TheDemoUserEmail SELECT LAST_INSERT_ID() as NewReportID; ENDOpen in new window
As a workaround approach: if you can modify the caller code to supply a document instead, you can both
- detect if a key/column exists in the supplied document to omit updates where a key is not present and
- 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 endNote the above pattern is limited to a single table pattern of the form...
...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...
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...
1 | foo | NULL |
2 | bar | init |