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?
@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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Create YourProc (
@Search varchar(100),
@Category varchar(10) = NULL,
@Section varchar(10) = NULL,
)
as
SELECT * From CLS WHERE
Category=ISNULL(@Category,
AND Section=ISNULL(@Section,Se
AND Description LIKE '%' + @Search + '%'
note: changed the order of the parameters to put the required on first.