Sql-server – Parameterize table name in dynamic SQL

sql serverstored-procedures

I have been working on a few stored procedures that have conditional parameters to them, but one of them is giving me a problem that I can't quite figure out. This is the code for the procedure:

CREATE PROCEDURE dbo.GetTableData(
    @TblName   VARCHAR(50),
    @Condition VARCHAR(MAX) = NULL,
) AS
BEGIN
    IF(EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TblName))
        BEGIN
            DECLARE @SQL NVARCHAR(MAX) = N'
            SELECT * FROM @TblName WHERE 1=1'
            + CASE WHERE @Condition IS NOT NULL THEN
            ' AND ' + @Condition ELSE N'' END

            DECLARE @params NVARCHAR(MAX) = N'
                @TblName   VARCHAR(50),
                @Condition VARCHAR(MAX)';

            PRINT @SQL

            EXEC sys.sp_executesql @SQL, @params,
                @TblName,
                @Condition
        END
    ELSE
        RETURN 1
END

The way I would like the procedure to work, is that it's suppose to allow me to do quick table look ups. So if I want to see everything from my Parts table, I'd just run

EXEC GetTableData 'parts'

Or if I wanted to see everything in the Parts table with a specific supplier I'd run

EXEC GetTableData 'parts', 'supplier LIKE ''A2A Systems'''

Now in the above example, when I run it, the PRINT @SQL line prints out the query as:

SELECT * FROM @TblName WHERE 1=1 AND supplier LIKE 'A2A Systems'

So the query it being put together properly (it seems).

However after it prints I am getting the following error:

Msg 1087, Level 16, State 1, Line 4

Must declare the table variable "@TblName"

I still get this error if I change the EXEC line to:

EXEC GetTableData @TblName='parts', @Condition='supplier LIKE ''A2A Systems'''

So what am I doing wrong here? Why isn't it taking my @TblName variable value?

Best Answer

You need to modify your procedure this way:

CREATE PROCEDURE dbo.GetTableData(
@TblName   VARCHAR(50),
@Condition VARCHAR(MAX) = NULL
) AS
BEGIN
    IF(EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TblName))
        BEGIN
            DECLARE @SQL NVARCHAR(MAX) = N'
            SELECT * FROM ' + @TblName + 'WHERE 1=1'
        + CASE WHEN @Condition IS NOT NULL THEN
        ' AND ' + @Condition ELSE N'' END

        DECLARE @params NVARCHAR(MAX) = N'
            @TblName   VARCHAR(50),
            @Condition VARCHAR(MAX)';

        PRINT @SQL

        EXEC sys.sp_executesql @SQL, @params,
            @TblName,
            @Condition
    END
ELSE
    RETURN 1
END

Your variable @TblName must not be inside the @SQL string