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 sensitive problem

You simply need to change the collation in the WHERE clause so that it is
case sensitive. The following example illustrates how the COLLATE clause ca
n
be used to define the collation:
CREATE TABLE [Function]
(
[Function] VARCHAR(100)
)
INSERT [Function] SELECT 'dr'
INSERT [Function] SELECT 'DR'
INSERT [Function] SELECT 'Dr'
SELECT *
FROM [Function]
WHERE [Function] = 'Dr'
Returns:
Function
dr
DR
Dr
(3 row(s) affected)
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Function
Dr
(1 row(s) affected)
HTH
- Peter Ward
WARDY IT Solutions
"bad_boyu" wrote:

> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>To add on to Peter's response, you can also include the case-insensitive
predicate so that an index on the column can be used:
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' AND
[Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||Hello,
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BB|||You simply need to change the collation in the WHERE clause so that it is
case sensitive. The following example illustrates how the COLLATE clause ca
n
be used to define the collation:
CREATE TABLE [Function]
(
[Function] VARCHAR(100)
)
INSERT [Function] SELECT 'dr'
INSERT [Function] SELECT 'DR'
INSERT [Function] SELECT 'Dr'
SELECT *
FROM [Function]
WHERE [Function] = 'Dr'
Returns:
Function
dr
DR
Dr
(3 row(s) affected)
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Function
Dr
(1 row(s) affected)
HTH
- Peter Ward
WARDY IT Solutions
"bad_boyu" wrote:

> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||To add on to Peter's response, you can also include the case-insensitive
predicate so that an index on the column can be used:
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' AND
[Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||Thanks a lot for these replies!
But I have another problem, I need that the method LIKE or something
similar to be case-sensitive! The Function column has fields like this:
"Dr Bad Boy", "DR Angelina",
"Prof dr Italian", "Prof Dr Adrian",...
Is there any solution for this kind of problem?
Best regards,
BB
Dan Guzman wrote:
> To add on to Peter's response, you can also include the case-insensitive
> predicate so that an index on the column can be used:
> SELECT *
> FROM [Function]
> WHERE [Function] = 'Dr' AND
> [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||See if this helps:
http://vyaskn.tripod.com/case_sensi..._sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
Hello,
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BB|||On 14 Jun 2006 02:08:55 -0700, bad_boyu wrote:

>Thanks a lot for these replies!
>But I have another problem, I need that the method LIKE or something
>similar to be case-sensitive! The Function column has fields like this:
>"Dr Bad Boy", "DR Angelina",
>"Prof dr Italian", "Prof Dr Adrian",...
>Is there any solution for this kind of problem?
Hi BB,
SELECT *
FROM Function
WHERE Function LIKE '%Dr%' COLLATE LATIN1_General_CS_AS
Hugo Kornelis, SQL Server MVP|||Thanks for these quick replies!!! I believe it works ;)
You are the best!|||Thanks a lot for these replies!
But I have another problem, I need that the method LIKE or something
similar to be case-sensitive! The Function column has fields like this:
"Dr Bad Boy", "DR Angelina",
"Prof dr Italian", "Prof Dr Adrian",...
Is there any solution for this kind of problem?
Best regards,
BB
Dan Guzman wrote:
> To add on to Peter's response, you can also include the case-insensitive
> predicate so that an index on the column can be used:
> SELECT *
> FROM [Function]
> WHERE [Function] = 'Dr' AND
> [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP