SQL Case Select

[MS Forum Newsgroup User]

Hope you can help, I thought this would work but doesn't,

SELECT getdata= case(@who)
When 'me' then (select col1, col2, col3)
When 'him' then (select col4, col5, col6)
When 'them' then (select col1, col3, col5)
else '' end

From dbo.mytbl Where ID= @ID

End

Thanks for any help!

[Plamen Ratchev]

CASE expressions return a scalar value with singe data type. If your columns are compatible data type then you can write
the query as follows:

SELECT CASE @who WHEN 'me' THEN col1
WHEN 'him' THEN col4
WHEN 'them' THEN col1
ELSE ''
END AS col1,
CASE @who WHEN 'me' THEN col2
WHEN 'him' THEN col5
WHEN 'them' THEN col3
ELSE ''
END AS col2,
CASE @who WHEN 'me' THEN col3
WHEN 'him' THEN col6
WHEN 'them' THEN col5
ELSE ''
END AS col3
FROM dbo.mytbl
WHERE ID = @ID;

--
Plamen Ratchev
http://www.SQLStudio.com

[--CELKO--]

Let us start with basic programming and then get into SQL. CASE is an
expression. An expression has one and only one data type and returns
one and only one scalar value.

Now for the SQL:
1) SELECT always has a FROM; SELECT by itself is dialect
2) Please be consistent with capitalization so people can read your
code
3) RDBMS has no magical, universal "id" because it follows the logical
Law of Identity -- to be is to be something in particular; to be
nothing in particular or everything in general is to be nothing at
all.
4) Do not use the old proprietary "<column name> = <expression>"
syntax. Please learn that it does not create rows, as you tried to
do.

Here is a stinky kludge:

SELECT CASE
WHEN @who IN ('me', 'them')
THEN col1
WHEN @who = 'him'
THEN col4
ELSE '' END AS magic_something_1,

CASE @who
WHEN 'me' THEN col2

WHEN 'them' THEN col3
WHEN 'him' THEN col5
THEN ELSE '' END AS magic_something_2,

CASE @who
WHEN 'me' THEN col3

WHEN 'them' THEN col5
WHEN 'him' THEN co6
THEN ELSE '' END AS magic_something_3

FROM Foobar
WHERE vague_magic_id = @in_vague_magic_id

Remember that freshman course on Software Engineering? The
fundamentals of this craft? Flag coupling -- the worst way to write
code in any language? "Britney Spears, Squids and Automobiles" is a
phrase that refers to a piece of crappy code that has no cohesion --
you have no idea what it does until run time.

[MS Forum Newsgroup User]

Thanks Plamen,

Does this mean you are not able to use a separate SQL for a case?

Regards,

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:iqCdnRkWzeAntpXW...@speakeasy.net...

[Plamen Ratchev]

CASE is not a control of flow element and it can only return a scalar value. If you need to execute conditional SQL then
you can use IF...ELSE

IF @who = 'me'
SELECT ...
ELSE IF @who = 'him'
SELECT ...
ELSE IF @who = 'them'
SELECT ...

[MS Forum Newsgroup User]

Plamen,

That's what I was looking for thanks again, I was afraid that the If is not
available in SQL.

Thanks!

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message

news:iqCdnRgWzeAg-JXW...@speakeasy.net...