CODEWITHSUNDEEP
sql-server-2008stored-proceduresoptional-parameters
larryq
larryq

Reputation: 16299

Optional parameters in SQL Server stored procedure

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.

Upvotes: 194

Views: 390230

Answers (4)

Bruce Patin
Bruce Patin

Reputation: 355

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

Upvotes: 5

billpennock
billpennock

Reputation: 460

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

Upvotes: 10

Raj More
Raj More

Reputation: 48018

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

Upvotes: 289

Mike Cole
Mike Cole

Reputation: 14703

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

Upvotes: 78

Related Questions