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:
>

Case statement

Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via SQLMonster.com" wrote:
> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1
>

Case statement

Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200603/1
|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via droptable.com" wrote:

> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200603/1
>

Case statement

Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1Not the way you're doing it. You can have something along the lines of:
UPDATE t1
SET
f1_status = CASE
WHEN f1_status = 0 THEN 1
ELSE f1_status END
, f1_created = CASE
WHEN f1_status = 0 THEN getdate()
ELSE f1_created END
WHERE t1id = 1
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5df3ff92c5d28@.uwe...
Is it possible to have multiple expressions, or dml expressions in a case
statement similar to the following?
DECLARE @.path_f1 nvarchar(200)
DECLARE @.path_f2 nvarchar(200)
SELECT
@.path_f1 = output_path_file1,
@.path_f2 = output_path_file2
FROM t1
WHERE t1id = 1
SELECT
CASE
WHEN f1_status = 0 THEN
UPDATE t1
SET f1_status = 1, f1_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f1, null
WHEN f1_status = 1 AND f2_status = 0 THEN
UPDATE t1
SET file2_status = 1, file2_created = getdate()
WHERE t1id = 1
EXEC usp_t1 @.path_f2, null
ELSE
RAISERROR ('Error Message', 16,1)
END
FROM t1
WHERE t1id = 1
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200603/1|||The issue would become very straightforward and simple only if one stops
using the phrase 'a CASE statement' because there is no such a thing, and
starts using 'a CASE expression.'
Linchi
"cbrichards via droptable.com" wrote:

> Is it possible to have multiple expressions, or dml expressions in a case
> statement similar to the following?
> DECLARE @.path_f1 nvarchar(200)
> DECLARE @.path_f2 nvarchar(200)
> SELECT
> @.path_f1 = output_path_file1,
> @.path_f2 = output_path_file2
> FROM t1
> WHERE t1id = 1
> SELECT
> CASE
> WHEN f1_status = 0 THEN
> UPDATE t1
> SET f1_status = 1, f1_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f1, null
> WHEN f1_status = 1 AND f2_status = 0 THEN
> UPDATE t1
> SET file2_status = 1, file2_created = getdate()
> WHERE t1id = 1
> EXEC usp_t1 @.path_f2, null
> ELSE
> RAISERROR ('Error Message', 16,1)
> END
> FROM t1
> WHERE t1id = 1
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200603/1
>sql