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

7

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.

edited May 26, 2016 at 18:29

answered May 26, 2016 at 18:19

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Optional parameters in stored procedure in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related