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!

1

4 Answers

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

2

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

5

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

1

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

I often use optional parms 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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy