How to execute a sql resultset from a table variable

Question

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.

Thom A · Accepted Answer · 2022-01-07 12:06:11Z

1

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;

answered Jan 7, 2022 at 12:06

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

1 Comment

lucian howard Over a year ago

Works like a charm, simple and quick. Now I can run this before I kick off my adf pipelines because we currently have blocks on our warehouse tables because multiple people create their own reports directly from this warehouse IE powerbi, tableau. Much appreciated Larnu

Collectives™ on Stack Overflow

How to execute a sql resultset from a table variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related