CASE inside FROM clause

[JahDW]

Hello!

I'm getting a syntax error when I try to put a CSE statement inside my FROM clause... is this possible?

Basically I want the query to use a different table depending on the @service parameter that the user inputs.

select column1, column2, ...
from
(CASE
WHEN @service = 'AV' THEN 'no_sav'
WHEN @service = 'ISS' THEN 'no_iss'
WHEN @service = 'SMS' THEN 'no_sms'
END)
where ...

Thanks,

Dan

 

[LKBrwnDBA]

I don't think ANY sql implementation will allow such a construct.

You need to pre-create the sql statement in a string variable and then execute that string.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[rac2]

Or write a procedure using

IF @service = 'AV'
BEGIN
  SELECT * FROM no_sav
END

ELSE IF @service = 'ISS'
BEGIN
  SELECT * FROM no_iss
END

ELSE
BEGIN
  SELECT * FROM no_sms
END

using the corresponding Procedure Language for your particular RDBMS.

Or place the conditions in a WHERE clause in a UNION query.

SELECT * FROM no_sav
WHERE @service = 'AV'

UNION

SELECT * FROM no_iss
WHERE @service = 'ISS'

UNION

SELECT * FROM no_sms
WHERE @service = 'SMS'