CODEWITHSUNDEEP
sqlsql-servert-sql
Jobin Joseph
Jobin Joseph

Reputation: 123

Execute dynamic query result using stored procedure

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');

Upvotes: 1

Views: 82

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28900

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

Upvotes: 1

gofr1
gofr1

Reputation: 15977

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'

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

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)

Upvotes: 1

Related Questions