Dynamic SQL mistakenly looks for table variable

my code accepts table name parameters and my dynamic code is supposed to query a table. I consistently get the error: @sourcetbl variable must be declared. After a lot of troubleshooting, I realized why I get error. SQL keeps assuming that I must pass on a table parameter. But I don’t need/want table param. I have string for a table and I want it to query the table.

My code:

DECLARE @DBname AS VARCHAR(30), @TblSchema AS VARCHAR(4) , @TblName AS VARCHAR(30), @LoadDate as INT
SET @DBname='Test'
SET @TblName='myTable'
SET @TblSchema='dbo' 

DECLARE @Tblnamestring AS VARCHAR(100) 

SET @Tblnamestring =' ' + @DBname + '.' + @TblSchema+ '.' + @TblName

SET @SQLCodes= N'
; WITH CTE AS (
        SELECT *
            FROM QUOTENAME(@dynamicTblName) S
            WHERE 
                S.Loaddate > ''@Loaddate''
        ) 
        SELECT * FROM CTE'

EXEC SP_EXECUTESQL 
    @STMT=@SQLCodes,
    @PARAMS=N'@dynamicTblName AS VARCHAR(100)',
    @dynamicTblName=@Tblnamestring; 

I constantly get the error that my variable (@dynamicTblName) is not defined. So here is what I tried:

1. I tried adding QUOTENAME beside it.
2. I tried using EXEC as follow

    SET @SQLCodes= N'
    ; WITH CTE AS (
            SELECT *
                FROM QUOTENAME(@Tblnamestring) S
                WHERE 
    S.Loaddate > '@Loaddate'
            ) 
            SELECT * FROM CTE'
    
    EXEC (@SQLCodes) 

3. Then I just got rid of SP_EXECUTESQL‘s other params. And I just ran it without those.
4. I ran SP_EXECUTESQL with @Tblnamestring being defined outside.

None of these work. SQL Expects a table variable. But I am just giving a string that I need it to query.

Answer

This:

DECLARE @Tblnamestring AS VARCHAR(100) 

SET @Tblnamestring =' ' + @DBname + '.' + @TblSchema+ '.' + @TblName

SET @SQLCodes = N'
    ; WITH CTE AS (
        SELECT *
        FROM QUOTENAME(@dynamicTblName) S
        WHERE S.Loaddate > @Loaddate
    ) 
    SELECT * FROM CTE'

Should be this:

DECLARE @Tblnamestring AS VARCHAR(100);

SET @Tblnamestring = QUOTENAME(@DBname) + '.' + QUOTENAME(@TblSchema) + '.' + QUOTENAME(@TblName);

SET @SQLCodes = N'
    WITH CTE AS (
        SELECT *
        FROM ' + @dynamicTblName + ' S
        WHERE S.Loaddate > @Loaddate
    ) 
    SELECT *
    FROM CTE;';

The entire point of using dynamic SQL is to insert the table name into the SQL, giving a string which can be run as static SQL 🙂

And this:

EXEC SP_EXECUTESQL 
    @STMT = @SQLCodes,
    @PARAMS = N'@dynamicTblName AS VARCHAR(100)',
    @dynamicTblName = @Tblnamestring; 

Should be:

EXEC SP_EXECUTESQL 
    @STMT = @SQLCodes,
    @PARAMS = N'@Loaddate AS INT',
    @Loaddate = @Loaddate;

Note the semi-colon is a statement terminator. Many people put it before WITH because they aren’t terminating their statements correctly since SQL Server is sometimes forgiving.