Can a CASE Staement be used in the WHERE clause

I have myself and need some help clarifing how to weite the
following statement so that 3 difierent results can come from the value of
the passed Parameter. The parameter can have 0,1 or 2 as the values
What I'm trying to get back is the records that are automated if the value
is 1 or the records if the value is manual 0 other wise return both types of
records
SELECT Info.ID,
Info.Automated
FROM Info
WHERE (Info.Automated LIKE @.Auto))
Can a case statement be used in the where clause?
I want to change the Like to = if possible.
Thanks for the helpJim Abel (JimAbel@.discussions.microsoft.com) writes:
> I have myself and need some help clarifing how to weite the
> following statement so that 3 difierent results can come from the value
> of the passed Parameter. The parameter can have 0,1 or 2 as the values
> What I'm trying to get back is the records that are automated if the
> value is 1 or the records if the value is manual 0 other wise return
> both types of records
> SELECT Info.ID,
> Info.Automated
> FROM Info
> WHERE (Info.Automated LIKE @.Auto))
> Can a case statement be used in the where clause?
> I want to change the Like to = if possible.
There is no CASE statement in T-SQL, but there is a CASE expression. And,
yes, it can be used in WHERE clauses. I'm not sure that I understood
exactly what you wanted to do, but here is an example:
SELECT ..
FROM Info
WHERE Automated LIKE CASE WHEN @.param = 1 THEN @.Auto
WHEN @.param = 2 THEN @.Bil
WHEN @.param = 3 THEN @.Macchina
END
You find more information in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks this is helping. Let me see know if I can get you to understand what
I'm trying to achive with the query because I'm not sure if a CASE expressio
n
is the right thing to do.
SELECT Info.ID, Info.Automated
FROM Info
WHERE (info.Automated = CASE
WHEN @.Auto = 0 THEN
audPolicyDetail.Auyomated = 0
WHEN @.Auto = 1 THEN
audPolicyDetail.Auyomated = 1
WHEN @.Auto = 2 THEN
audPolicyDetail.Auyomated in(0,1)))
END
"Erland Sommarskog" wrote:

> Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> There is no CASE statement in T-SQL, but there is a CASE expression. And,
> yes, it can be used in WHERE clauses. I'm not sure that I understood
> exactly what you wanted to do, but here is an example:
> SELECT ..
> FROM Info
> WHERE Automated LIKE CASE WHEN @.param = 1 THEN @.Auto
> WHEN @.param = 2 THEN @.Bil
> WHEN @.param = 3 THEN @.Macchina
> END
> You find more information in Books Online.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Thanks this is helping. Let me see know if I can get you to understand
> what I'm trying to achive with the query because I'm not sure if a CASE
> expression is the right thing to do.
> SELECT Info.ID, Info.Automated
> FROM Info
> WHERE (info.Automated = CASE
> WHEN @.Auto = 0 THEN
> audPolicyDetail.Auyomated = 0
> WHEN @.Auto = 1 THEN
> audPolicyDetail.Auyomated = 1
> WHEN @.Auto = 2 THEN
> audPolicyDetail.Auyomated in(0,1)))
> END
I don't know what you are trying to achieve, but the above is not
correct. A CASE expression must return one of the data types in SQL Server,
and boolean is not a data type in SQL Server.
If also looks funny with audPolicyDetail, as there is no such table in
the query. Is this a CLR UDT column?
The general recommendation for query problems is that you post:
o CREATE TABLE statement(s) for your table(s).
o INSERT statements with sample data.
o The desired result given the sample.
Including this sort of information often helps to clarify what you are
looking for, and reduces the amount of guessing needed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You missed one of the fundamental principels of programming, not just
SQL.
SQL has a CASE **expression** and not a CASE **statement**; expressions
return a single value of a known data type. It does not change the
control flow and in fact since SQL is a declarative language, there is
no control flow concept.
Did you mean something like this? A bit weird, but valid:
SELECT I.vague_id, automated_status
FROM Info AS I. AudPolicyDetail AS A
WHERE A.automated_status
= CASE @.auto
WHEN 0 THEN 0
WHEN 1 THEN 1
ELSE I.automated_status END;|||Ok, Ill try once more then we can drop it. I have a page that passed the
@.Auto parameter. On the page the parameter can have one of three options
(All, True or False). In the SQL Statement I think of these options as 3
different possible WHERE conditions as shown below.
1) WHERE Automated = 0 -- Returns records that are not automated
2) WHERE Automated = 1 -- Returns records that are automated
3) -- does not use the Automated field -- returns all the records
It is in the 3rd option that I get stuck with the syntax of the statement
how do I write a WHERE clause that can both use the Automated field and then
not use it if all records are to be returned?
I appreciate your patients with me on this.
Thanks for all the help
"Erland Sommarskog" wrote:

> Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> I don't know what you are trying to achieve, but the above is not
> correct. A CASE expression must return one of the data types in SQL Server
,
> and boolean is not a data type in SQL Server.
> If also looks funny with audPolicyDetail, as there is no such table in
> the query. Is this a CLR UDT column?
> The general recommendation for query problems is that you post:
> o CREATE TABLE statement(s) for your table(s).
> o INSERT statements with sample data.
> o The desired result given the sample.
> Including this sort of information often helps to clarify what you are
> looking for, and reduces the amount of guessing needed.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Ok, Ill try once more then we can drop it. I have a page that passed the
> @.Auto parameter. On the page the parameter can have one of three options
> (All, True or False). In the SQL Statement I think of these options as 3
> different possible WHERE conditions as shown below.
> 1) WHERE Automated = 0 -- Returns records that are not automated
> 2) WHERE Automated = 1 -- Returns records that are automated
> 3) -- does not use the Automated field -- returns all the records
> It is in the 3rd option that I get stuck with the syntax of the
> statement how do I write a WHERE clause that can both use the Automated
> field and then not use it if all records are to be returned?
The most common way to write it would be
WHERE (Automated = @.Auto OR @.Auto IS NULL)
But if you prefer to pass 2 meaning "don't matter, this works too:
WHERE (Automated = @.Auto OR @.Auto = 2)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx