optional parameters in SQL Server stored proc

Question

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

User · Answer

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

User · Answer

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

User · Answer

You can declare like this  CREATE PROCEDURE MyProcName      Parameter1 INT   1       Parameter2 VARCHAR  100     StringValue        Parameter3 VARCHAR  100    NULL AS     check for the NULL   default value  indicating nothing was passed    if   Parameter3 IS NULL  BEGIN        whatever code you desire for a missing parameter       INSERT INTO          END     and use it in the query as so   SELECT   FROM Table WHERE Column    Parameter

[sql-server-2008] optional parameters in SQL Server stored proc?

Examples related to sql-server-2008

Examples related to stored-procedures

Examples related to optional-parameters