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