Dynamically Selecting from a Table Variable in SQL Stored Procedure

Question

I'm working with SQL Server and facing an issue with dynamic SQL in a stored procedure. My goal is to count the number of rows in a table, but the table name needs to be dynamically determined based on a variable.

DECLARE @varCount BIGINT
SELECT @varCount = COUNT(1)
FROM dbo.[table-staging]

IF(@varCount > 0)
BEGIN
    -- Other operations
END

In the above, dbo.[table-staging] is hardcoded, but I want to replace it with a variable, say @staging. I tried the following approach, but it doesn't seem to work:

SET @varCount = 'SELECT @varCount = COUNT(1) FROM ' + @staging;

Answer 1

You can use below code to store the result of dynamic SQL query in variable.

CREATE PROCEDURE CountRowsInTable
    @staging VARCHAR(100)
AS
BEGIN
    DECLARE @varCount BIGINT;
    DECLARE @sql NVARCHAR(MAX);

    SET @sql = 'SELECT @varCount = COUNT(1) FROM ' + QUOTENAME(@staging);
    EXEC sp_executesql @sql, N'@varCount BIGINT OUTPUT', @varCount OUTPUT;

    SELECT @varCount;

    IF(@varCount > 0)
        BEGIN
            -- Other operations
        END;
END;