Optional parameters in SQL Server stored procedure

Question

I'm writing some stored procedures in SQL Server 2008. Is the concept of optional input parameters possible here?

I suppose I could always pass in NULL for parameters I don't want to use, check the value in the stored procedure, and then take things from there, but I was interested if the concept is available here.

Take a long read at Erland's site, he has some great info on dynamic search conditions: http://www.sommarskog.se/dyn-search.html — Aaron Bertrand, Nov 28 '09 at 16:55

score 274 · Accepted Answer · edited Jan 08 '23 at 23:34

274

You can declare it 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

edited Jan 08 '23 at 23:34

Peter Mortensen

30,738
21
105
131

answered Nov 27 '09 at 21:19

Raj More

47,048
33
131
198

1

what if the parameter is type of uniqueidentifier? ex. @userId uniqueidentifier – R K Sharma Dec 04 '15 at 12:25
1

Answering @RKSharma for anyone who's wondering too - it works just the same with uniqueidentifiers. – Maximilian Riegler Mar 29 '16 at 12:21

score 76 · Answer 2 · edited Jan 08 '23 at 23:32

76

Yes, it is. Declare the 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).

edited Jan 08 '23 at 23:32

Peter Mortensen

30,738
21
105
131

answered Nov 27 '09 at 21:11

Mike Cole

14,474
28
114
194

You don't even need the `= NULL` – OMG Ponies Nov 27 '09 at 21:19
4

Are you sure you don't need it? – Mike Cole Nov 28 '09 at 02:16
62

OMG Ponies, if you don't include = , then the parameter will be required. You can pass it in as NULL, but then you just shift that logic to the application(s) that use the procedure. – Aaron Bertrand Nov 28 '09 at 16:54
14

Adding to Aaron's point. It is better to use the " = NULL" if you are adding a new optional parameter to an existing stored proc. The reason is, you may not be aware of ALL the code that calls this proc. Hence, unless you make it optional using the " = NULL", for all the places that you may have missed to pass in a value, it will break. – nanonerd Jun 08 '17 at 12:50
1

nanonerd: 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. At least that's the way it worked for me in 2014 with – billpennock Nov 15 '18 at 18:14

score 9 · Answer 3 · edited Jan 08 '23 at 23:37

9

In SQL Server 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 third parameter is added as optional. Execution (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

edited Jan 08 '23 at 23:37

Peter Mortensen

30,738
21
105
131

answered Nov 15 '18 at 18:22

billpennock

441
1
6
14

1

Sweet! Last time I tried this was with Sql 2012, and back then, it still required you to pass in NULL to get the default value. Nice to know they finally took out that requirement and made them truly **optional** . – eidylon Nov 19 '20 at 17:42

score 3 · Answer 4 · edited Jan 08 '23 at 23:40

The default mentioned in previous answers only works for simple cases. In more complicated cases, I use an IF clause near the beginning of the stored procedure to provide a value, if the parameter is NULL or empty and calculations are required.

I often use optional parameters in the WHERE clause, and discovered that SQL does not short circuit logic, so use a CASE statement to make sure not to try to evaluate NULL or empty dates or unique identifiers, like so:

CREATE Procedure ActivityReport
(
    @FromDate varchar(50) = NULL,
    @ToDate varchar(50) = NULL
)

AS

SET ARITHABORT ON

IF @ToDate IS NULL OR @ToDate = '' BEGIN
    SET @ToDate = CONVERT(varchar, GETDATE(), 101)
END

SELECT ActivityDate, Details
FROM Activity
WHERE
1 = CASE
   WHEN @FromDate IS NULL THEN 1
   WHEN @FromDate = '' THEN 1
   WHEN ActivityDate >= @FromDate AND ActivityDate < DATEADD(DD,1,@ToDate) THEN 1
   ELSE 0
END

Optional parameters in SQL Server stored procedure

4 Answers4

Linked

Related