optional parameters in SQL Server stored proc?

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).


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