June 26, 2012 at 11:51 am
hi, i'm having trouble with this for some reason:
there are five options in this field, they are:
A
B
C
D
E
i made a variable @var
when the user sets @var = A then it should return A and B
when the user sets @var = B then it should return B
when the user sets @var = C then it should return C
when the user sets @var = D then it should return D
when the user sets @var = E then it should return E
how can I do this?
thanks!!
-martin
June 26, 2012 at 12:20 pm
i got it guys, the answer for those searching later is:
and
(
[FIELD] = case
when @var = A then A
when @var = B then B
when @var = C then C
when @var = D then D
when @var = E then E end
or
[FIELD] = case
when @var = A then B
when @var = B then B
when @var = C then C
when @var = D then D
when @var = E then E end
)
---------------------------
thanks for any time spent on this.
-m
June 26, 2012 at 12:39 pm
If you only have these cases, then I would do this:
IF @var <> 'A'
BEGIN
SELECT whatever
FROM table
WHERE [field]=@var
END
ELSE
BEGIN
SELECT whatever
FROM table
WHERE [field]='A' OR [field]='B'
Of course, you could flip those if you wanted, depending on how you expect the future of the request to go.
Jared
CE - Microsoft
June 26, 2012 at 1:06 pm
SELECT whatever
FROM table
WHERE [field]=@var
UNION ALL
SELECT whatever
FROM table
WHERE @var = 'A' and field = 'B'
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
June 26, 2012 at 1:14 pm
ChrisM@home (6/26/2012)
SELECT whateverFROM table
WHERE [field]=@var
UNION ALL
SELECT whatever
FROM table
WHERE @var = 'A' and field = 'B'
Nicely done... 🙂
Jared
CE - Microsoft
June 26, 2012 at 1:19 pm
You can also use a structure like this:
WHERE 1 = CASE
WHEN @var = A AND [column] IN (A, B) THEN 1
WHEN [column] = @var THEN 1
ELSE 0
END
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply