The below query will return 60 rows, how can I execute the statements using stored procedure in a single go.
SELECT 'UPDATE TEMP1
SET ' + COLUMN_NAME + '= 0' + ' WHERE '+COLUMN_NAME+ ' IS NULL'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TEMP1' AND DATA_TYPE = 'FLOAT'
AND COLUMN_NAME NOT IN('Sales','Net_Margin');
Here is one way to do it:
DECLARE @Sql nvarchar(max) = ''
SELECT @Sql = @Sql + 'UPDATE TEMP1
SET ' + COLUMN_NAME + '= 0' + '
WHERE '+COLUMN_NAME+ ' IS NULL;
'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TEMP1' AND DATA_TYPE = 'FLOAT'
AND COLUMN_NAME NOT IN('Sales','Net_Margin');
PRINT @Sql -- remark this row once you check the sql and unremark the next one to execute
--EXEC(@Sql)
At first create SP:
CREATE PROCEDURE SPName
@table_name sysname,
@data_type sysname
AS
BEGIN
DECLARE @sql nvarchar(max)
SELECT @sql = @sql + 'UPDATE TEMP1 SET ' + COLUMN_NAME + '= 0' + ' WHERE '+COLUMN_NAME+ ' IS NULL;'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name AND DATA_TYPE = @data_type
AND COLUMN_NAME NOT IN('Sales','Net_Margin')
EXEC sp_execute @sql
END
Then execute:
EXEC SPName 'TEMP1', 'FLOAT'
Try adding a semicolon to your query or a GO keyword and do like below
declare @sql nvarchar(max)
set @sql =
(SELECT 'UPDATE TEMP1
SET ' + COLUMN_NAME + '= 0' + ' WHERE '+COLUMN_NAME+ ' IS NULL'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TEMP1' AND DATA_TYPE = 'FLOAT'
AND COLUMN_NAME NOT IN('Sales','Net_Margin');)
exec(@Sql)
print @sql
