Link to home
Start Free TrialLog in
Avatar of johnywhite
johnywhite

asked on

Optional Parameters in SELECT

I have a procedure:
@Category varchar(10),
@Section varchar(10),
@Search varchar(100)
as
SELECT * From CLS WHERE Category=@Category AND @Section=Section AND Description LIKE '%' + @Search + '%'

What I want to do is if nothing is put in for say @Category, then it ignores the entire Category=@Category portion.  I have tried using something like how I did the Description, but the problem with that is there are some Categories that have similar values, such as SLC and SLC1, so doing it that way sometimes returns SLC1 when I only wanted SLC. Any ways of doing this?
ASKER CERTIFIED SOLUTION
Avatar of dready
dready

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try this:

Create YourProc (
@Search varchar(100),
@Category varchar(10) = NULL,
@Section varchar(10) = NULL,
)
as
SELECT * From CLS WHERE
 Category=ISNULL(@Category,Category)
 AND Section=ISNULL(@Section,Section)
 AND Description LIKE '%' + @Search + '%'

note: changed the order of the parameters to put the required on first.
Avatar of johnywhite
johnywhite

ASKER

Hmm, it looks like I accidentally posted this twice. I would give the points to you dgmg, but dready posted the same solution in my other post so I think I should give the points to him.  Any disagreements?
Nope.