optional parameters in SQL Server stored proc?

Question

Hi everyone,

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!

Answer 1

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

Answer 2

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