SQL Server Dynamic SQL - Get output from list of tables

Question

I am trying to loop through a temp table variable that contains a list of table names. I want to simply count the rows in each table where a DateTracked column is greater than 30 days. I am having trouble dynamically changing the FROM @tblName variable to store the record count and then insert it into my tracking table. Eventually I will use a cursor to loop through each, but I just want to get this logic down first for a single table. Here is my test code:

DECLARE @tblName as NVARCHAR(MAX)
DECLARE @q as NVARCHAR(MAX)
SET @q = 'Select Count(DateTracked) FROM Audit.' + @tblName + ' WHERE DateTracked > DATEADD(dd, -30, CAST(GETDATE() as date))'

--DECLARE @tblNameTable TABLE
--(
--  tableName NVARCHAR(MAX)
--)
--INSERT INTO @tblNameTable VALUES (N'myTestTable')

DECLARE @ExpectedRecordsToMove AS TABLE (col1 int)
INSERT INTO @ExpectedRecordsToMove EXECUTE sp_executesql @q, N'@tblName nvarchar(500)', @tblName = 'myTestTable'

SELECT * FROM @ExpectedRecordsToMove

You cannot parameterize @q since you cannot parameterize table names even in dynamic SQL; the whole statement must be generated dynamically, since @tblName must be interpolated into the text. If you set @tblName before producing @q you have the same effect as you'd have in a loop. — Jeroen Mostert
– Jeroen Mostert, Commented May 3, 2021 at 12:58
No, that's precisely what will not work. Again, table names cannot be parameters. 'SELECT ...' + @tblName + ' WHERE ...' is correct, but then @tblName must already have a value there, and should not be passed as a parameter. In your example, DECLARE @tblName AS SYSNAME = 'myTestTable'. — Jeroen Mostert
– Jeroen Mostert, Commented May 3, 2021 at 13:02
I understand now. How can I take the output of sp_executesql once I have that statement generated and insert it into a variable that I can reference? — Data Dill
– Data Dill, Commented May 3, 2021 at 13:07
Not sure what the question is there -- INSERT ... EXEC works with table variables, so the remainder of the code is fine. The INSERT of course should not happen inside the dynamically generated statement, as then you can't reference the outer scope's variables. — Jeroen Mostert
– Jeroen Mostert, Commented May 3, 2021 at 13:10
I understand now. I was declaring a local variable and NOT a temp table variable. It seems to work now. — Data Dill
– Data Dill, Commented May 3, 2021 at 13:16

4b0 · Accepted Answer · 2021-05-03 13:22:03Z

4

Found solution.

DECLARE @tblName as NVARCHAR(MAX) = 'tblAutoDispatch_DispatchStatus_Map_Tracking'
DECLARE @q as NVARCHAR(MAX) = 'SELECT Count(DateTracked) FROM Audit.' + @tblName + ' WHERE DateTracked > DATEADD(dd, -30, CAST(GETDATE() as date))'

DECLARE @ExpectedRecordsToMove TABLE
(
    ExpectedRecordsToMove Int
)
INSERT INTO @ExpectedRecordsToMove
EXECUTE sp_executesql @q

SELECT * FROM @ExpectedRecordsToMove

_{Note: Answer provided by OP on question.}

answered May 3, 2021 at 13:22

community wiki

4b0

Sign up to request clarification or add additional context in comments.

Comments

Martin Perez · Accepted Answer · 2021-05-03 13:51:20Z

0

sp_executesql also allows for output parameters. It is possible to assign variable inside the inner query and return the value.

DECLARE @tblName AS NVARCHAR(MAX) = N'tblAutoDispatch_DispatchStatus_Map_Tracking';
DECLARE @q AS NVARCHAR(MAX)
    = N'SELECT @rowcount = COUNT(DateTracked) FROM Audit.' + @tblName
      + N' WHERE DateTracked > DATEADD(dd, -30, CAST(GETDATE() as date))';

DECLARE @rowcount INT;

EXECUTE sp_executesql
    @query = @q,
    @parameters = N'@rowcount INT OUTPUT',
    @rowcount = @rowcount OUTPUT;

SELECT
    @rowcount;

answered May 3, 2021 at 13:51

Martin Perez

213 bronze badges

Comments

Charlieface · Accepted Answer · 2021-05-03 16:05:50Z

0

You can actually do this without a cursor, by building up the query in one go

You must use QUOTENAME to ensure table names do not cause syntax errors
You cannot parameterize a table name, it must be inserted directly into dynamic SQL

DECLARE @tablenames TABLE (schemaName sysname, tblName sysname);
--  insert schema and table names

DECLARE @sql nvarchar(max) = N'
SELECT
    ISNULL(tblName, ''Grand Total'') AS tblName,
    SUM(rowcount) AS rowcount
FROM (
' +
(
  SELECT STRING_AGG(CAST(
N'    SELECT ' + QUOTENAME(tn.schemaName, '''') + N'.' + QUOTENAME(tn.tblName, '''') + N' AS tblName,
           Count(DateTracked) AS rowCount
    FROM ' + QUOTENAME(tn.schemaName) + N'.' + QUOTENAME(tn.tblName) + N'
    WHERE DateTracked > DATEADD(dd, -30, CAST(GETDATE() as date))'
  AS nvarchar(max)), N'
UNION ALL
')
  FROM @tablenames tn
  JOIN sys.tables t ON tn.schemaName = SCHEMA_NAME(t.schema_id) AND tn.tblName = t.name
) + N'
) AS tables
GROUP BY ROLLUP(tblName);
';

PRINT @sql;  -- for testing
EXEC (@sql);

If you don't have STRING_AGG on your version of SQL Server, you must use FOR XML instead

answered May 3, 2021 at 16:05

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

1 Comment

Data Dill Over a year ago

I didn't describe this in the original post, but I'm using a cursor to loop through 200 tables and a WHILE LOOP to batch the deletes/OUTPUT so I'm okay using a cursor in this scenario since there is no performance impact and I am still using a SET based operation for my actual transactions.

Collectives™ on Stack Overflow

SQL Server Dynamic SQL - Get output from list of tables

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related