How to execute a sql resultset from a table variable-CodePudding

I have a simple query

SELECT 'kill '   ''''   session_id   '''' FROM sys.dm_pdw_waits
where object_name like 'dw_prd.F%'

union

SELECT 'kill '   ''''   session_id   '''' FROM sys.dm_pdw_waits
where object_name like 'dw_prd.D%'

This is the resultset

enter image description here

I want to execute the result set as is, meaning all the kills should execute. I tried making this a variable and executing it but it doesn't execute

What's the easiest way to execute this result set.

CodePudding user response：

You would have to use dynamic SQL here. One method would be to aggregate the strings, and then execute it as one batch. According to the documentation, Azure Synapse supports both STRING_AGG and sys.sp_executesql, however, I have no way of checking this actually works:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10);

SELECT @SQL = STRING_AGG(N'KILL '   QUOTENAME(session_id, N'''')   N';',@CRLF)
FROM sys.dm_pdw_waits
WHERE object_name LIKE 'dw_prd.[FD]%';

EXEC sys.sp_executesql @SQL;