CODEWITHSUNDEEP
sqlsql-servert-sql
AliseS
AliseS

Reputation: 31

Table has sql statements in each row. How do I run each in order?

I am using Microsoft SQL Server. I have a table in my database. The first line is an Item # in numerical order and second column is exact lines of T-SQL that I need to run.

How do I write something that calls each line to run in the order of the item number? 

 Item #       Code
 ------       ----
   1          UPDATE GOC_AU_INTERSECTION SET GOC_AU_INTERSECTION.DSMT_BUSINESS = 'BUSA',
              GOC_AU_INTERSECTION.RULE_CODE = '101'  FROM DSMT_GOC_FLAT, GOC_AU_INTERSECTION                     
              WHERE GOC_AU_INTERSECTION.RULE_CODE Is Null AND DSMT_GOC_FLAT.MS_Level_10='14197'           
              AND DSMT_GOC_FLAT.GOC = GOC_AU_INTERSECTION.GOC;  

   2          UPDATE GOC_AU_INTERSECTION SET GOC_AU_INTERSECTION.DSMT_BUSINESS = 'BUSA',           
              GOC_AU_INTERSECTION.RULE_CODE = '102' FROM DSMT_GOC_FLAT, GOC_AU_INTERSECTION            
              WHERE GOC_AU_INTERSECTION.RULE_CODE Is Null AND DSMT_GOC_FLAT.MS_Level_10='14198'             
              AND DSMT_GOC_FLAT.GOC = GOC_AU_INTERSECTION.GOC;     

   3          UPDATE GOC_AU_INTERSECTION SET GOC_AU_INTERSECTION.DSMT_BUSINESS = 'BUSA', 
              GOC_AU_INTERSECTION.RULE_CODE = '103' FROM DSMT_GOC_FLAT, GOC_AU_INTERSECTION 
              WHERE GOC_AU_INTERSECTION.RULE_CODE Is Null AND DSMT_GOC_FLAT.MS_Level_10='14202' 
              AND DSMT_GOC_FLAT.GOC = GOC_AU_INTERSECTION.GOC ;

Upvotes: 3

Views: 60

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93754

Simplest way is 

  declare @sql nvarchar(max)=''
  select @sql+= Code+' ' from tablename order by Item#
  exec sp_executesql  @sql

@DanielGimenez += is a valid TSQL operator. check this

DECLARE @string varchar(10) = 'abc'

SET @string += 'def'

print @string

Upvotes: 1

gotqn
gotqn

Reputation: 43676

I hate CURSORS, so I would offer you do build a dynamic SQL statement and then execute it. This is full working example:

SET NOCOUNT ON
GO

    DECLARE @Statement TABLE
    (
        [Item #] TINYINT IDENTITY(1,1)
       ,[Code] NVARCHAR(32)
    )

    INSERT INTO @Statement ([Code])
    VALUES ('SELECT 1')
          ,('SELECT 2')
          ,('SELECT 3')

    DECLARE @DynamicSQLStatement NVARCHAR(MAX)

    SET @DynamicSQLStatement =
    (
        SELECT CHAR(10) + CHAR(13) + [Code] + CHAR(10) + CHAR(13)
        FROM @Statement
        ORDER BY [Item #]
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')

    --SELECT @DynamicSQLStatement

    EXEC sp_executesql @DynamicSQLStatement


SET NOCOUNT OFF
GO

Upvotes: 1

Daniel Gimenez
Daniel Gimenez

Reputation: 20679

You can do this with a cursor and sp_executesql

DECLARE @sqlStmt VARCHAR(4000);

DECLARE exCursor CURSOR FOR
SELECT Column2 FROM table ORDER BY Column1;

OPEN exCursor;

FETCH NEXT FROM exCursor INTO @sqlStmt;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXECUTE sp_executesql @sqlStmt;

    FETCH NEXT FROM exCursor INTO @sqlStmt;
END

CLOSE exCursor;
DEALLOCATE exCursor;

The cursor will be created to query from your statement table and retrieve your sql statements, ordering by column1. You then get each statement and execute it.

Read More about it

Another thing about sp_executesql is that you can pass parameters too it. I noticed your statement in quite repetitive - you could have one table with queries and one with parameters. Could be a good idea if this is a long term solution.

Upvotes: 2

Related Questions