Variable name in select statement

Question

I have several tables for storing various information about files, such as thumbs, images, data tables, ...

I am writing a stored procedure to get the file name of a specific identifier. sort of:

    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 with a statement case whenor just use the table name as a variable?

Answer 1

You cannot use the case .. when you need to switch between the table in the FROM clause (as you can in the conditionalORDER BY ). i.e. as follows:

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

will not work.

, SQL. , , , @id :

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

Sql, , unparameterized, (, @table), Sql Injection. , , @table , , @table .

Answer 2

SQL EXECUTE it

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

Answer 3

CREATE PROCEDURE get_file_name(
    @id int,
    @table nvarchar(50)
    )as

DECLARE @SQL nvarchar(max);
SET @SQL = 'select [filename] from ' + @table + ' where id = ' + @id
EXECUTE (@SQL)