optional parameters in SQL Server stored proc?

user_172617 0
Viewed 304471

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!

optional-parameters sql-server-2008 stored-procedures
4 Answers

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
user_47359730

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
user_7864840
Related