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!

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

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.

Thanks Plamen,

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

Regards,

news:iqCdnRkWzeAntpXWnZ2dnUVZ_tJi4p2d@speakeasy.net...

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

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

Plamen,

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

Thanks!

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