Optional Parameters in Sql Server Stored Proc?
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...
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!
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
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).
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
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
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
