I'm writing some stored procs in SQL Server 2008, and wondered if the concept of optional input parameters is possible here?
I suppose I could always pass in NULL for parameters I don't want to use, check the value in the stored proc, then take things from there, but I was interested if the concept is available here. Thanks!
This question is related to
sql-server-2008
stored-procedures
optional-parameters
2014 and above at least you can set a default and it will take that and NOT error when you do not pass that parameter. Partial Example: the 3rd parameter is added as optional. exec of the actual procedure with only the first two parameters worked fine
exec getlist 47,1,0
create procedure getlist
@convId int,
@SortOrder int,
@contestantsOnly bit = 0
as
Yes, it is. Declare parameter as so:
@Sort varchar(50) = NULL
Now you don't even have to pass the parameter in. It will default to NULL (or whatever you choose to default to).
Source: Stackoverflow.com