Optional Parameters in SQL Stored Procedures

10 Comments

• Hey Rob, which one has the better execution plan, the one that you shared or this:
  
  
  
  
  
  CREATE PROCEDURE TestProc
  
  (
  
  @Param1 varchar(50) = NULL,
  
  @Param2 varchar(50) = NULL,
  
  @Param3 varchar(50) = NULL
  
  )
  
  AS
  
  SELECT
  
  *
  
  
  
  FROM
  
  TestTable
  
  
  
  WHERE col1 = ISNull( @Param1,col1 )
  
  AND col2 = ISNull( @Param2,col2 )
  
  AND col3 = ISNull( @Param3,col3 )
  
  

• Another variation to consider is anyone's doing execution plans:
  
  
  
  WHERE @Param1 IN (Col1, NULL) ...
  

• Shouldn't is be something like the following?
  
  
  
  CREATE PROCEDURE TestProc
  
  (
  
  @Param1 varchar(50) = NULL,
  
  @Param2 varchar(50) = NULL,
  
  @Param3 varchar(50) = NULL
  
  )
  
  AS
  
  SELECT
  
  *
  
  
  
  FROM
  
  TestTable
  
  
  
  WHERE
  
  ((@Param1 is null) OR (col1 = @Param1)) AND
  
  ((@Param1 is null) OR (col2 = @Param2)) AND
  
  ((@Param1 is null) OR (col3 = @Param3))
  
  
  
  Or am I missing out here????
  
  
  
  Doing the 1=1 will always return all rows if I am correct. So basicly you could just run
  
  
  
  SELECT *
  
  FROM TestTable
  
  
  
  Which will have the best execution plan I think :)
  
  
  
  

• This makes no sense, did you actually test it?
  
  
  
  The option Darren offers works, but only if there are no NULL values in the columns.
  
  
  
  (you would need ISNULL also on the column, and COALESCE in the where statement)
  

• Ramon's sql is what I've always seen and used. I don't know if its "best", but it sure beats yours, which doesn't work. :)
  

• Sorry... I copy/paste @Param1 two times too many :)
  
  
  
  CREATE PROCEDURE TestProc
  
  (
  
  @Param1 varchar(50) = NULL,
  
  @Param2 varchar(50) = NULL,
  
  @Param3 varchar(50) = NULL
  
  )
  
  AS
  
  SELECT
  
  *
  
  
  
  FROM
  
  TestTable
  
  
  
  WHERE
  
  ((@Param1 is null) OR (col1 = @Param1)) AND
  
  ((@Param2 is null) OR (col2 = @Param2)) AND
  
  ((@Param3 is null) OR (col3 = @Param3))
  
  
  
  I don't know what you want to accomplish with your query yours will definitaly will always return all rows.
  
  
  
  WHERE
  
  (col1 = @Param1 OR 1=1) AND
  
  (col2 = @Param2 OR 1=1) AND
  
  (col3 = @Param3 OR 1=1)
  
  
  
  Is just the same as
  
  
  
  WHERE
  
  TRUE AND
  
  TRUE AND
  
  TRUE
  
  
  
  Thus every record will be returned. And I don't think that is what you are after.
  
  
  
  @Paul Wilson: This search query will work perfectly and based on 100% on hands experience :)
  
  
  
  The only problem that I have with optional parameters for search storedprocedures is that they will degrade your query performance dramatically if you use a lot of paramters. It is just better build the query in some sort of dataaccess component. Because SQL server will save the execution plan performance won't suffer from this. Only problem with this is that some architects think this is wrong.. If that is the case then just pass a varchar with the storedprocedures that you execute with
  
  
  
  declare @query nvarchar(4000)
  
  set @query = 'SELECT * FROM testtable'
  
  exec sp_executesql @query
  
  
  
  :)
  
  

• Like Ramon, this is how I would do it:
  
  
  
  create procedure testproc
  
  ( @Param1 varchar(50) = NULL, @Param2 varchar(50) = NULL, @Param3 varchar(50) = NULL )
  
  
  
  as
  
  
  
  select *
  
  from testable
  
  where ((@Param1 is null) or (col1 = @Param1))
  
  and ((@Param1 is null) or (col2 = @Param2))
  
  and ((@Param1 is null) or (col3 = @Param3))
  
  
  
  The way Rob did will optimize to a simple select * from testable
  
  

• Frans - regarding the changes you suggest, this technique does not work where col1, col2 or col3 are null when ANSI_NULLS is ON. AFAIK Ramon's code does handle null columns properly.
  

• Joseph:
  
  hmm.. you're probably right. Too bad, as COALESCE is a bit faster than the IS NULL OR .. constructs (as far as I can tell from the execution plan)
  

• Simon,
  
  
  
  Unfortunately the right solution is the wrong solution because it opens up an unacceptable attack vector on my application. I do not allow any direct operations on my tables. The link you provided stated the security risk up front, and said it may not be acceptable for that very reason. Given the requirements, your option is not valid.