jebar8 jebar8 - 1 year ago 107
SQL Question

Dynamic update statement with variable column names

We're looking to do an update in several SQL Server databases to change all NULL values in a certain table to be empty strings instead of NULL. We're potentially going to be doing this across hundreds of databases. The table name will always be the same, but the column names are variable based on how the front-end application is configured (don't judge... I didn't create this system).

Is there a way to do an update on all of these columns without knowing the column names ahead of time?

Answer Source

You can pass the name of the column in dynamic sql:

declare @sql nvarchar (1000);
set @sql = N'update table set ' + @column_name + '= ''''';

exec sp_executesql @sql;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download