Optional parameters in stored procedure in SQL Server
First of all you should provide default values of parameters and then compare those to column values or to null:
create procedure somename
@p1 int,
@p2 int = null,
@p3 int = null
as
begin
select * from sometable
where col1 = @p1 and
(col2 = @p2 or @p2 is null) and
(col3 = @p3 or @p3 is null)
end
Then you can call the proc like:
exec somename @p1 = 1
exec somename @p1 = 1, @p2 = 2
exec somename @p1 = 1, @p2 = 2, @p3 = 3
And it will work as you expect. With your approach you will actually need 2^N
IF
checks where N
is number of such parameters. Imagine that for 5 parameters you will need to do 2^5 = 32
IF
checks. With provided approach you will have all in one query and only 5 checks.
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Admin
Updated on June 15, 2022Comments
-
Admin about 2 years
I am new to Stack Overflow as well as stored procedures. I would like to set up a procedure to include three optional parameters and three dependent parameters. If the user does not provide those optional parameters, then the result set would return all possible given the three dependent.
In my mind it looks something similar to:
@Color1,--optional @Color2,--optional @Color3,--optional @Date,--dependent @Item,--dependent @Price--dependent IF @Color1 IS NULL OR @Color2 IS NULL OR @Color3 IS NULL THEN EXEC (SELECT * WHERE Date = @Date AND Item = @Item AND Price = @Price) ELSE EXEC (SELECT * WHERE Color1 = @Color1 AND Color2 = @Color2 AND Color3 = @Color3 AND Date = @Date AND Item = @Item AND Price = @Price)
but I'm still learning. Thanks in advance for any help