CODEWITHSUNDEEP
sql-server-2008stored-proceduresoptional-parameters
user6387225
user6387225

Reputation: 25

Optional parameters in stored procedure in SQL Server

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

Upvotes: 2

Views: 16514

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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.

Upvotes: 7

Related Questions