create select statement based on case statement in sql

Question

Is there a clean way in sql to create a select statement based on a case statement?

I'm looking for the basic logic to be something like

   CASE @tableToUse
           WHEN 'Table1' SELECT * FROM Table1 
           WHEN 'Table2' SELECT * FROM table2 
           DEFAULT 'Table3' SELECT * FROM table3
   END CASE

Answer 1

In T-Sql (MS SQL Server) you can dynamically compose your SQL statement and then use the sp_executesql method to execute it. Based on your case statement you can build a SELECT statement using a variable and then use sp_executesql to run it. The one thing to be aware of is that you need to make sure you clean any input you get from users if you are using text directly from users to prevent SQL injection attacks.

For example,

DECLARE @sql NVARCHAR(200)

SELECT @sql = CASE @tableToUse
    WHEN 'Table1' THEN 'SELECT * FROM Table1'
    WHEN 'Table2' THEN 'SELECT * FROM Table2'
    ELSE 'Table3' THEN 'SELECT * FROM Table2'
    END

EXECUTE sp_executesql @sql

You should be able to do something similar in other versions of SQL.

Answer 2

Why not just join to the testTable on username?

Answer 3

Sorry, Case statements in tSQL (which I assume you are asking about) are for evaluating expressions only and can't be used for control flow.

You are pretty much stuck with IF..THEN

IF (@Something='Table1')
    SELECT Field1 from table1
ELSE
   IF (@Something='Table2')
        SELECT Field1 from table2
   ELSE
        SELECT Field1 From table3

Answer 4

You can do this ! only that you cannot do a "select *" as that might return more than one value that shoot an error even if that has to be a table with a single column

SELECT   columnName = CASE WHEN 'foo' THEN select <Column_Name> from tableName where columnName = 'this'  
                           WHEN 'bar' THEN select <Column_Name>  from tableName where columnName = 'that' 
END 
FROM tableName