CASE statement with an IN

hi there,
I am trying to do this, it would simply my sql so much, but the syntax won't
parse.
DECLARE @.Tmp varchar
--@.Tmp will either be cow or pig after something done here, lets say pig for
simplification..
SET @.Tmp = 'pig'
SELECT * From Blah WHERE SomeField IN (CASE @.Tmp WHEN 1 THEN (1) WHEN 2 THEN
(1,2) END)
To explain, i have a value of @.Tmp. It will either be 'cow' or 'pig'. I want
to use one select statement but the WHERE clause changes depending on the
value of @.Tmp.
If it is cow, then I just want the clause to be WHERE SomeField IN(1), if it
is pig then I want the clause to be WHERE SomeField IN (1,2). I have tried
lots of ways, locations of @.Tmp, brackets etc, and it just wont work. Is thi
s
simply not possible or have I got the syntax wrong? it doesnt seem an
unlikely thing to want to do...
I even tried ditching the idea and doing an if and rewriting the whole
select statement depending on @.Tmp. Only problem is it is the select
statement for a cursor FOR statement, so fiddling around with that is tricky
too !!!Hi Louise
Try something like
SELECT * From Blah
WHERE ( SomeField = 1 AND @.tmp = 'cow')
OR ( SomeField = 2 AND @.tmp = 'pig')
John
"louise raisbeck" wrote:

> hi there,
> I am trying to do this, it would simply my sql so much, but the syntax won
't
> parse.
> DECLARE @.Tmp varchar
> --@.Tmp will either be cow or pig after something done here, lets say pig f
or
> simplification..
> SET @.Tmp = 'pig'
> SELECT * From Blah WHERE SomeField IN (CASE @.Tmp WHEN 1 THEN (1) WHEN 2 TH
EN
> (1,2) END)
> To explain, i have a value of @.Tmp. It will either be 'cow' or 'pig'. I wa
nt
> to use one select statement but the WHERE clause changes depending on the
> value of @.Tmp.
> If it is cow, then I just want the clause to be WHERE SomeField IN(1), if
it
> is pig then I want the clause to be WHERE SomeField IN (1,2). I have tried
> lots of ways, locations of @.Tmp, brackets etc, and it just wont work. Is t
his
> simply not possible or have I got the syntax wrong? it doesnt seem an
> unlikely thing to want to do...
> I even tried ditching the idea and doing an if and rewriting the whole
> select statement depending on @.Tmp. Only problem is it is the select
> statement for a cursor FOR statement, so fiddling around with that is tric
ky
> too !!!|||ok, that works [blush]!! thanks.
"John Bell" wrote:
> Hi Louise
> Try something like
> SELECT * From Blah
> WHERE ( SomeField = 1 AND @.tmp = 'cow')
> OR ( SomeField = 2 AND @.tmp = 'pig')
> John
> "louise raisbeck" wrote:
>|||It looks like you are going to have to construct your SQL statement in a
string variable and then use EXEC() to submit the string to the query parser
.
SET @.strSQL = 'SELECT * From Blah WHERE SomeField IN (' + @.Tmp + ')'
EXEC(@.strSQL)
HTH,
Mike
"louise raisbeck" wrote:

> hi there,
> I am trying to do this, it would simply my sql so much, but the syntax won
't
> parse.
> DECLARE @.Tmp varchar
> --@.Tmp will either be cow or pig after something done here, lets say pig f
or
> simplification..
> SET @.Tmp = 'pig'
> SELECT * From Blah WHERE SomeField IN (CASE @.Tmp WHEN 1 THEN (1) WHEN 2 TH
EN
> (1,2) END)
> To explain, i have a value of @.Tmp. It will either be 'cow' or 'pig'. I wa
nt
> to use one select statement but the WHERE clause changes depending on the
> value of @.Tmp.
> If it is cow, then I just want the clause to be WHERE SomeField IN(1), if
it
> is pig then I want the clause to be WHERE SomeField IN (1,2). I have tried
> lots of ways, locations of @.Tmp, brackets etc, and it just wont work. Is t
his
> simply not possible or have I got the syntax wrong? it doesnt seem an
> unlikely thing to want to do...
> I even tried ditching the idea and doing an if and rewriting the whole
> select statement depending on @.Tmp. Only problem is it is the select
> statement for a cursor FOR statement, so fiddling around with that is tric
ky
> too !!!|||yes that is another solution. thanks. does exec (string) have speed
implications?
"Mike Austin" wrote:
> It looks like you are going to have to construct your SQL statement in a
> string variable and then use EXEC() to submit the string to the query pars
er.
> SET @.strSQL = 'SELECT * From Blah WHERE SomeField IN (' + @.Tmp + ')'
> EXEC(@.strSQL)
> HTH,
> Mike
> "louise raisbeck" wrote:
>|||Certainly, this solution is slower than if you're able to come up with a way
to implement dynamic SQL in a fixed statement.
Another approach may be:
IF @.Tmp = 'Pig'
BEGIN
SELECT...
END
ELSE
BEGIN
SELECT...
END
"louise raisbeck" wrote:
> yes that is another solution. thanks. does exec (string) have speed
> implications?
> "Mike Austin" wrote:
>