Optional parameters in stored procedure in SQL Server

Question

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

Giorgi Nakeuri · Accepted Answer · 2016-05-26 18:29:39Z

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.

Collectives™ on Stack Overflow

Optional parameters in stored procedure in SQL Server

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related