Execute SQL statements in results generated by dynamic SQL statement

Question

I've created a dynamic SQL script to generate statements to replace empty strings with NULL for every column in every table in my database. I've stored the script to the variable @SQL.

When I run EXEC @SQL, it generates the following results:

(No column name)
`UPDATE [TableX] SET [ColumnA] = NULL WHERE [ColumnA] =''`
`UPDATE [TableX] SET [ColumnB] = NULL WHERE [ColumnB] =''`
`UPDATE [TableX] SET [ColumnC] = NULL WHERE [ColumnC] =''`
`UPDATE [TableY] SET [ColumnA] = NULL WHERE [ColumnA] =''`
`UPDATE [TableY] SET [ColumnB] = NULL WHERE [ColumnB] =''`
`UPDATE [TableY] SET [ColumnB] = NULL WHERE [ColumnB] =''`

And so on... (there is an inconsistent/unknown number of columns and tables, and therefore an inconsistent/unknown number of results).

My problem is that rather than simply returning these statements as results, I want to actually execute all of the individual statements. I would be very grateful if someone could advise me on the easiest way to do so.

Thanks in advance!

Copy and paste the resulting cells into a new query window, and execute it... or since you already know how to `EXEC @SQL`, consider how you might do that with the result set. — underscore_d, Apr 13 '21 at 09:18
Why do 3 `UPDATE` against `TableX` and not 1? What have you tried to execute the statements? Have you googled "executing dynamic statements SQL Server"? — Thom A, Apr 13 '21 at 09:18
Include your query. It's totally possible to construct a single update statement for each table and execute it. — Stu, Apr 13 '21 at 09:48

score 0 · Answer 1 · answered Apr 13 '21 at 10:09

EXEC @SQL will not return the results in your question unless @SQL is the name of a stored procedure. It seems you are actually using EXEC (@SQL) to execute a batch of SELECT statements, each of which returns a single column, single row result with a column containing an UPDATE statement.

Below is example code you can add to the end of your existing script to concatenate the multiple result sets into a single batch of statements for execution.

DECLARE @UpdateStatementsBatch nvarchar(MAX);
DECLARE @UpdateStatementsTable TABLE(UpdateStatement nvarchar(MAX));

INSERT INTO @UpdateStatementsTable
    EXEC(@SQL);

SELECT @UpdateStatementsBatch = STRING_AGG(UpdateStatement,N';') + N';'
FROM @UpdateStatementsTable;

EXEC (@UpdateStatementsBatch);

It's probably better to modify your existing code to build the batch of update statements rather than concatenate after the fact but I can't provide an example without seeing your existing code.

score 0 · Answer 2 · answered Apr 13 '21 at 11:35

Here's a suggestion for something you can try to build a single update statement per table.

Obviously I have no idea what you've built to construct your existing sql but you should be able to tweak to your requirements.

Basically concatenate all columns for all tables where they are a varchar datatype. If they are an empty string, update to null, otherwise retain current value.

I don't know what version of Sql server you have so I've used for xml for compatability, replace with string_agg if supported. Add any additional filtering eg for only specific tables.

with c as (
    select c.table_name, cols=Stuff(
    (select concat(',',QuoteName(column_name),' = ','iif(', QuoteName(column_name), '='''', NULL, ', QuoteName(column_name), ')',Char(10))
    from information_schema.columns c2
    where data_type in ('varchar','nvarchar') and c2.table_name=c.table_name
    for xml path('') 
    ),1,1,'')
    from information_schema.columns c
    group by c.table_name
)
select concat('update ', QuoteName(c.table_name), ' set ', cols,';')
from c
where cols is not null

As this is presumably a one-off data fix you can just cut and paste the resulting sql into SSMS and run it.

Alternatively you could add another level of concatenation and exec it if you wanted to make it something you can repeat.

Execute SQL statements in results generated by dynamic SQL statement

2 Answers2