SQL Server 2012 – sp_executesql Multiple Parameter Error Solution

sql serversql-server-2012

I am receiving an error when attempting to execute the following code that builds a dynamic string to be executed by sp_executesql. 

DECLARE @paramDefinition NVARCHAR(500);
DECLARE @ID1 NVARCHAR(20);
DECLARE @StageTable1 NVARCHAR(100);
DECLARE @StageTable2 NVARCHAR(100);

SET @ID1 = '111111111111111111';
SET @StageTable1 = 'TestTable1';
SET @StageTable2 = 'TestTable2';

DECLARE @sqlToExecute NVARCHAR(MAX)

SET @tableCreateSQL = 'SELECT @ID1
                       INTO   @StageTable1 
                       FROM   @StageTable2 ';

SET @paramDefinition = '@ID1 NVARCHAR(20), 
                        @StageTable1 NVARCHAR(100), 
                        @StageTable2 NVARCHAR(100)';

EXEC sys.sp_executesql @tableCreateSQL, 
                       @paramDefinition, 
                       @ID1,
                       @StageTable1, 
                       @StageTable2;

The error I receive states the following:

Incorrect syntax near '@StageTable1'.

Am I incorrectly constructing the SQL string, or is there an issue with the sp_executesql statement?

Best Answer

You can't parameterize column and table names, sorry. You'll need to do this the old way:

SET @tableCreateSQL = N'SELECT ' + QUOTENAME(@ID1) + N'
               INTO   dbo.' + QUOTENAME(@StageTable1) + N'
               FROM   dbo.' + QUOTENAME(@StageTable2) + N';';

Also see:

Related Question