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!
14 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 = @ParameterYes, it is. Declare parameter as so:
@Sort varchar(50) = NULLNow you don't even have to pass the parameter in. It will default to NULL (or whatever you choose to default to).
52014 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
asThe 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
