CODEWITHSUNDEEP
sqlsql-servert-sqldynamic-sql
Rajesh Vaddala
Rajesh Vaddala

Reputation: 43

EXEC sp_executesql not working as expected

EXEC SP_EXECUTESQL is giving me the error

Must declare the table variable "@TABLE"

How can we resolve this?

TESTTABLE contains 6 table names in it. I have to get all the table names from TESTTABLES and delete the records from all 6 tables based on matching criteria. 

CREATE PROCEDURE TEST_SCHEMA.TEST_SP
@P_DATE   CHAR(10),
@P_TIME   CHAR(8),
@P_MILSEC CHAR(6),
@P_RECORD   CHAR(1),
@P_NODE     CHAR(2)

AS

BEGIN

DECLARE @SQL AS NVARCHAR(MAX);
DECLARE @zTABLE VARCHAR(10);
DECLARE @ParmDefinition NVARCHAR(MAX);

DECLARE TABLES_LIST CURSOR FOR SELECT TBL_NAME FROM TESTTABLE WITH 
(NOLOCK);

OPEN TABLES_LIST

FETCH FROM TABLES_LIST INTO @zTABLE

WHILE @@FETCH_STATUS = 0  

BEGIN

SET @SQL = N'DELETE FROM @TABLE WHERE DATE = @DATE AND TIME = @TIME AND 
MILSEC = @MILSEC AND RECORD = @RECORD AND NODE = @NODE';


SET @ParmDefinition = N'@TABLE varchar(10), @DATE char(10), @TIME char(8), 
@MILSEC char(6), @RECORD char(1), @NODE char(2)';

EXEC SP_EXECUTESQL @SQL, @ParmDefinition, @zTABLE, @P_DATE, @P_TIME, 
@P_MILSEC, @P_RECORD, @P_NODE;

FETCH NEXT FROM TABLES_LIST INTO @zTABLE

END

CLOSE TABLES_LIST;

DEALLOCATE TABLES_LIST; 

END

When I'm running this it is giving above mentioned error. How can we rectify this error?

Upvotes: 2

Views: 2908

Answers (2)

Nisarg Shah
Nisarg Shah

Reputation: 14561

You cannot parameterize table names. So a delete query expecting a parameter in place of table name assumes that you are referring to a table variable named @TABLE.

Instead, you would need to append the table name in the query itself.

One of the ways of doing so would be to simply append to the SQL query:

SET @SQL = N'DELETE FROM ' + @zTABLE + ' WHERE KEY_DATE = @KEY_DATE AND KEY_TIME = @KEY_TIME 
    AND KEY_MILSEC = @KEY_MILSEC AND RECORDCD = @RECORDCD AND CRNODE = @CRNODE';

Alternatively, you could wrap it with QUOTENAME function to avoid SQL Injection through that variable:

SET @SQL = N'DELETE FROM ' + QUOTENAME(@zTABLE) + ' WHERE KEY_DATE = @KEY_DATE AND KEY_TIME = @KEY_TIME 
    AND KEY_MILSEC = @KEY_MILSEC AND RECORDCD = @RECORDCD AND CRNODE = @CRNODE';

Make sure to modify your @ParmDefinition and call to SP_EXECUTESQL as you no longer need to provide the @TABLE parameter.

Upvotes: 6

GeorgiG
GeorgiG

Reputation: 1101

Both:

SET @SQL

and 

SET @ParmDefinition

Include definitions of @TABLE. Without you declaring it, there is no way the SP would know what to execute. Perhaps you wanted to use @zTABLE, but you misswrote.

Upvotes: 0

Related Questions