"CLM" <CLM@discussions.microsoft.com> wrote in message
news:6834FDB4-3CF8-477B-BD0D-6D9757996F3F@microsoft.com...
> I have a stored procedure that I am using and feeding a parameter of 1, 2,
or 3
> 1 = criteriaA is null and criteriaB is null
> 2 = criteriaA is not null and criteriaB is not null
> 3 = criteriaA is null or is not null criteriaB is null or is not null
>
> can I
> IF @param = 1
> Begin
> select * from tmpTable where criteraA is null and criteriaB is null
> end
>
> IF @param = 2
> Begin
> select * from tmpTable where criteriaA is not null and criteriaB is not
> null
> end
>
> IF @param=3
> Begin
> select * from tmpTable where criteriaA is null or is not null and
> criteriaB is null or is not null
> end
>
> I know the syntax isn't 100% but will this kind of action work? I can
feed
> string parameters to the other criteria fields but the other fields are a
> linked table that if they are not present (is null) gives one set of
results
> as opposted to be being present (is not null) or a combined set of
results.
> There is no way to feed a string parameter to that type of criteria line.
>
> Any info would be greatly appreciated.
>
> CLM

Joe Celko wrote:

>Here is an example of how to use a CASE expression for complex logic.
>
>SELECT *
> FROM Foobar
> WHERE CASE
> WHEN @param = 1 -- both null
> AND COLEASE (criteraA, criteriaB) IS NULL
> THEN 1
> WHEN @param = 2 -- no nulls
> AND criteriaA IS NOT NULL
> AND criteriaB IS NOT NULL
> THEN 1
> WHEN @param = 3 -- one null
> AND COALESCE (criteriaA, criteriaB) IS NOT NULL
> THEN 1 ELSE 0 END = 1;
>
>--CELKO--
>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, datatypes, etc. in your
>schema are. Sample data is also a good idea, along with clear
>specifications.
>
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it!
>