how to make table names dynamic in SQL? [closed]

Posted on

Question :

I am trying to make the table name dynamic

declare @varString as nvarchar(10)
set @varString = '06232016'
declare @testString as nvarchar(500)
set @testString = 'SELECT  *  from ' +  'Cases_' + @varstring
execute sp_executesql @testString 
Select top 10 FROM @teststring

The first 5 lines of the code are working fine. Now how do I use the dynamic table name I just created? The last line is giving the error “Incorrect syntax near the keyword ‘FROM'”.

Answer :

The line that is failing is:

Select top 10 FROM @teststring

a) You’re missing the column names (or indeed *)

b) What you’re trying to do is not possible unless you use dynamic SQL

You use it in the exact way you are already using it.

-- Declare Table Name
DECLARE @tablename AS nvarchar(10);
SET @tablename = 'MyTestTable';

-- Execute a query
DECLARE @query_a AS nvarchar(500);
SET @query_a  = 'SELECT TOP 10  * FROM dbo.' +  @tablename;
EXECUTE sp_executesql @query_a;

-- Execute another query
DECLARE @query_b AS nvarchar(500);
SET @query_b = 'UPDATE dbo.' + @tablename + ' SET col_a = ''xyz''';
EXECUTE sp_executesql @query_b;

Warning: If you are inputting the tablename from a GUI, you need to sanitise the data to avoid SQL Injection attacks (https://msdn.microsoft.com/en-us/library/ms188001.aspx)

N.B. Use an ORDER BY when using TOP

Leave a Reply

Your email address will not be published.