Optional parameters in stored procedure

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

My problem is that I have three parameters is a stored procedure and all of
them optional. I can't think of avoiding writing a sub-sp for every
combination of provided and not provided parameters except to use 'like' as
below. I suspect that this may not have good performance compared to an
exact comparisoon (=). Is there a better to handle three parameters where
any combination of them could be optional? I am using the following sp;

CREATE PROCEDURE [dbo].[MyProc]
@Param1 varchar(50) ='',
@Param2 varchar(50) ='',
@Param3 varchar(50) =''
AS
SET NOCOUNT ON;
SELECT Col1, Col2, Col3
FROM MyTable
WHERE (Col1 like @Param1+ '%') AND (Col2 like @Param2+ '%') AND (Col3 like
@Param3+ '%')

Thanks

Regards
 
I think its quite simple:

step1: i would use null instead of empty strings as default values for
the parameters
step2: use this form: where (col1 like @param1 +'%' OR @param1 IS NULL
)

Easy as that...

Good luck.

Marcel van Eijkel
(www.vaneijkel.com)
 
If you want a Parameter to take on the stored procedure-set default value,
simply set the Parameter to Nothing in VB or Null in C#.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top