The table lists the sql statements on each line. How can I run each one in order?
I am using Microsoft SQL Server
. I have a table in my database. The first line is Item #
in numerical order and the second column is the exact lines T-SQL
I need to run.
How do I write something that calls each line to run in position number order?
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;
source to share
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;
A cursor will be created to query from the statement table and get your sql statements, ordered by column1. Then you receive each statement and execute it.
More about this
Another thing about sp_executesql
is that you can also pass parameters. I noticed your statement in a rather repetitive way: you can have one table with queries and one with parameters. It might be a good idea if this is a long term solution.
source to share
I hate CURSORS
, so I would suggest that you create a dynamic SQL statement and then execute it. This is a complete 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
source to share