CODEWITHSUNDEEP
sqlsql-server
Salek
Salek

Reputation: 303

Get table name from database field

I use SQL Server Management Studio 17.0. I have a table which controls separate tables for different files, something like:

 filename | tablename
 ---------+----------
 file1    | table1
 file2    | table2

I need to select from tablename, but not hardcoded. Filename comes from web, and I can fist get the tablename, like

select tablename 
from filetables 
where filename = "file1"

and use it to view the file table:

select * 
from (table1)

Is there any way to do it in SQL? Something like

Select * 
from 
    (select tablename 
     from filetables 
     where filename = "file1")

Upvotes: 4

Views: 645

Answers (2)

James Luxton
James Luxton

Reputation: 394

   DECLARE @v_sql        NVARCHAR(MAX),
           @v_table_name NVARCHAR(MAX),
           @v_file_name  NVARCHAR(MAX)

    SELECT @v_file_name  = 'file1' -- Populated from web proc

    SELECT @v_table_name = tablename
      FROM filetables
     WHERE filename = REPLACE(@v_file_name, '''', '') 

    SELECT @v_sql =

   'SELECT *
      FROM ' + @v_table_name + '
     WHERE filename = ''' + @v_file_name + ''''

    SELECT @v_sql -- Debug code to show you the statement prior to running

    EXEC sp_executesql @v_sql

You will need to utilize dynamic SQL like other users here have answered. Give this a shot in your environment and see how it goes.

Upvotes: -1

Icarus
Icarus

Reputation: 63956

You can't select from a table that can only be determined at run time and/or depending on some parameter. Your only option is to use a Dynamic SQL in this case but make sure you don't expose yourself to SQL-Injection attacks.

Here's a link on how to safely create Dynamic SQL.

Upvotes: 3

Related Questions