Case conditional action

Is it possible to check a variable in a case statement then perform a select
query depending on the variable? What I have tried is something like this
which does not work. aNyone know how this can be accomplished?
declare @.@.var int, @.rtn int
set @.var = 3
select case @.var when 1 select @.rtn = catid from categories
when 2 select @.rtn = itmid from categories
when 3 select @.rtn = catmid from categorymixbjamin wrote:
> Is it possible to check a variable in a case statement then perform a sele
ct
> query depending on the variable? What I have tried is something like this
> which does not work. aNyone know how this can be accomplished?
>
> declare @.@.var int, @.rtn int
> set @.var = 3
> select case @.var when 1 select @.rtn = catid from categories
> when 2 select @.rtn = itmid from categories
> when 3 select @.rtn = catmid from categorymix
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
A CASE expression is like this:
CASE <condition>
WHEN <evaluation result 1>
THEN <true result> ELSE <false result>
WHEN <evaluation result 1>
THEN <true result> ELSE <false result>
.. etc. ...
END
Use IF:
IF @.var = 1
SELECT @.rtn = catid FROM categories
IF @.var = 2
SELECT @.rtn = itmid FROM categories
IF @.var = 3
SELECT @.rtn = catmid FROM categorymix
These statements assume that there is only 1 row in each table, which is
probably wrong. What are you really trying to do?
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAy4TYechKqOuFEgEQKYbwCgzax4m6K6c5lD
CcRV3sxStCYnhlUAoPRg
pAwpcd7fD1BWfkIGeB21eKuG
=opZI
--END PGP SIGNATURE--|||bjamin (bjamin@.discussions.microsoft.com) writes:
> Is it possible to check a variable in a case statement then perform a
> select query depending on the variable? What I have tried is something
> like this which does not work. aNyone know how this can be
> accomplished?
There isn't any CASE statement in SQL. There is a CASE expression, which is
something different.

> declare @.@.var int, @.rtn int
> set @.var = 3
> select case @.var when 1 select @.rtn = catid from categories
> when 2 select @.rtn = itmid from categories
> when 3 select @.rtn = catmid from categorymix
Could write:
SELECT @.rtn = CASE WHEN 1 THEN (SELECT catid FROM categories)
WHEN 2 THEN (SELECT itmid FROM categories)
WHEN 3 THEN (SELECT catmid FROM categorymix)
EMD
Although, I think most people would prefer to use IF/ELSE in this case.
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