Execute a series of SQL commands

CREATE TYPE [dbo].[ExecSqlStatementsTableType] AS TABLE(
[RowId] [int] IDENTITY(1,1) NOT NULL,
[SqlCode] [nvarchar](max) NOT NULL
)

go

/*

declare @tblSqlCode dbo.ExecSqlStatementsTableType

insert into @tblSqlCode(SqlCode)

values('print ''task 1'''), ('print ''task 2''')

exec util.usp_ExecSqlStatements @TableSqlCode=@tblSqlCode

*/

CREATE proc [util].[usp_ExecSqlStatements](

@TableSqlCode dbo.ExecSqlStatementsTableType READONLY

,@verbose bit = 0

,@ExecSql bit = 1

)

as

-- ************************************************************************************************

-- ************************************************************************************************

set nocount on

-- ************************************************************************************************

-- ************************************************************************************************

-- ************************************************************************************************

declare @MaxRows int

declare @row int

declare @sql nvarchar(max)

-- ************************************************************************************************

-- ************************************************************************************************

-- ************************************************************************************************

select

@row = min(RowId)

,@MaxRows = max(RowId)

from

@TableSqlCode

-- ************************************************************************************************

while @row <= @MaxRows

begin

select

@sql = SqlCode

from

@TableSqlCode

where

RowId = @row

if @sql is not null

begin 

if @verbose=1

begin

print @sql

end

if @ExecSql=1

begin

exec sp_executesql @sql

end

end

set @row = @row +1

end