T-sql – Execute DDL statements from a query in stored procedure

stored-procedurest-sql

I would like to do this in a stored procedure:

  1. Query a specific database, dump results in a table
  2. The results contain DDL statements such as alter, create, IF exists…
  3. Execute the results from step 2 as if executing them in a query window via SSMS

Example of step 2 results are:

  1. Alter database…..
  2. exec sp_addmember….
  3. If not exists….

I'm couldn't find a good way to perform step 3, any thoughts?

Best Answer

Use a cursor to loop through the table, then use EXEC sys.sp_executesql for each row.

The following code relies on a temporary table, named #t, which contains various DML statements:

IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL
DROP TABLE #t;
CREATE TABLE #t 
(
    cmdOrder int NOT NULL IDENTITY(1,1)
    , cmd nvarchar(max) NOT NULL
);

INSERT INTO #t (cmd)
VALUES (N'SELECT 1')
    , (N'SELECT 2');

DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT t.cmd
FROM #t t
ORDER BY t.cmdOrder;
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @cmd;
    EXEC sys.sp_executesql @cmd;
    PRINT N'';
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

The output looks like:

enter image description here

Compound statements, like IF EXISTS (...) must occur in a single statement. If you need to run all rows as if they were a single statement, simply concatenate the output from each row, then use sys.sp_executesql on the result, as in:

IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL
DROP TABLE #t;
CREATE TABLE #t 
(
    cmdOrder int NOT NULL IDENTITY(1,1)
    , cmd nvarchar(max) NOT NULL
);

INSERT INTO #t (cmd)
VALUES (N'IF (1=2)')
    , (N'PRINT N''yes''')
    , (N'ELSE')
    , (N'PRINT N''no''');

DECLARE @line nvarchar(max);
DECLARE @cmd nvarchar(max) = '';
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT t.cmd
FROM #t t
ORDER BY t.cmdOrder;
OPEN cur;
FETCH NEXT FROM cur INTO @line;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = @cmd + CHAR(13) + CHAR(10) + @line;
    FETCH NEXT FROM cur INTO @line;
END
CLOSE cur;
DEALLOCATE cur;
EXEC sys.sp_executesql @cmd;

The output from the above code:

no

If you are allergic to cursors, you could use a set-based approach, such as:

DECLARE @cmd nvarchar(max) = '';
SELECT @cmd = CASE WHEN @cmd = '' THEN '' ELSE @cmd END + CHAR(13) + CHAR(10) + t.cmd
FROM #t t
ORDER BY t.cmdOrder;
EXEC sys.sp_executesql @cmd;

Understand there is a slight chance that using the set-based approach with string concatenation may cause the commands to be executed in the wrong order, even though there is an ORDER BY clause. The SQL Server query optimizer can do strange things under the hood if it thinks the query will perform faster.

Related Question