Variable table name in select statement

Question

I have some tables for storing different file information, like thumbs, images, datasheets, ...

I'm writing a stored procedure to retrieve filename of a specific ID. something like:

    CREATE PROCEDURE get_file_name(
    @id int,
    @table nvarchar(50)
    )as
    if @table='images'
        select [filename] from images
        where id = @id
    if @table='icons'
        select [filename] from icons
        where id = @id
....

How can I rewrite this procedure using case when statement or should I just use table name as variable?

Answer 1

You can't use case .. when to switch between a table in the FROM clause (like you can in a conditional ORDER BY). i.e. so the following:

select * from 
    case when 1=1
      then t1
      else t2
    end;

won't work.

So you'll need to use dynamic SQL. It's best to parameterize the query as far as possible, for example the @id value can be parameterized:

-- Validate @table is E ['images', 'icons', ... other valid names here]
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'select [filename] from **TABLE** where id = @id';
SET @sql = REPLACE(@sql, '**TABLE**', @table);

sp_executesql @sql, N'@id INT', @id = @id;

As with all dynamic Sql, note that unparameterized values which are substituted into the query (like @table), make the query vulnerable to Sql Injection attacks. As a result, I would suggest that you ensure that @table comes from a trusted source, or better still, the value of @table is compared to a white list of permissable tables prior to execution of the query.

Answer 2

Just build SQL string in another variable and EXECUTE it

DECLARE @sql AS NCHAR(500)
SET @sql=
    'SELECT [filename] '+
    ' FROM '+@table+
    ' WHERE id = @id'
EXECUTE(@sql)