USE master
GO
IF OBJECT_ID('sp_ExecBatchTerminated') IS NULL EXEC ('CREATE PROCEDURE sp_ExecBatchTerminated AS SELECT 1 AS ID')
GO
ALTER PROCEDURE sp_ExecBatchTerminated
@InputSQL VARCHAR(MAX) = NULL,
@SQL NVARCHAR(MAX) = '' OUTPUT,
@PrintStmtOnError BIT = 1,
@ReturnOnError BIT = 1,
@PrintSQL BIT = 0,
@ExecSQL BIT = 1,
@SetNocountOn BIT = 0,
@DbName SYSNAME = NULL,
@UseTryCatch BIT = 0,
@ErrorCount INT = 0 OUTPUT,
@BatchTerminator VARCHAR(30) = '
GO
'
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @InputSQL IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_ExecBatchTerminated',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN
END
SET @ErrorCount = 0 ;
WITH execs
AS (SELECT CASE WHEN @PrintSQL = 1 THEN 'PRINT ''-- Batch: ' + CAST(FieldNum AS VARCHAR) + '
' + REPLACE(Field, '''', '''''') + ''''
ELSE ''
END + CASE WHEN @UseTryCatch = 1 THEN 'BEGIN TRY
' ELSE ''
END + 'EXEC(''' + CASE WHEN @DbName <> '' THEN 'USE ' + @DbName + '
EXEC(''''' + REPLACE(Field, '''', '''''''''') + ''''')'
ELSE REPLACE(Field, '''', '''''')
END + ''')
' + CASE WHEN @UseTryCatch = 1 THEN 'END TRY
BEGIN CATCH
SET @ErrorCount = @ErrorCount + 1
' + CASE WHEN @PrintStmtOnError = 1
AND @PrintSQL = 0
OR @ReturnOnError = 1 THEN 'PRINT ''-- Batch: ' + CAST(FieldNum AS VARCHAR) + '
' + REPLACE(Field, '''', '''''') + ''''
ELSE ''
END + '
EXEC sp_ErrorHandler
@PrintError = 1
' + CASE WHEN @ReturnOnError = 1 THEN 'RETURN'
ELSE ''
END + '
END CATCH'
ELSE ''
END + CASE WHEN @UseTryCatch = 0
AND @PrintStmtOnError = 1
AND @PrintSQL = 0
OR @ReturnOnError = 1 THEN 'IF @@ERROR > 0 BEGIN
' + CASE WHEN @UseTryCatch = 0
AND @PrintStmtOnError = 1
AND @PrintSQL = 0 THEN ' PRINT ''-- Batch: ' + CAST(FieldNum AS VARCHAR) + '
' + REPLACE(Field, '''', '''''') + ''''
ELSE ''
END + CASE WHEN @UseTryCatch = 0
AND @ReturnOnError = 1 THEN '
RETURN' ELSE ''
END + '
END;' ELSE ''
END AS SQL
FROM Util.dbo.ParseDelimited(@InputSQL, @BatchTerminator) AS s)
SELECT @SQL = Util.dbo.StringConcat(SQL, '
')
FROM execs
IF @ExecSQL = 1
BEGIN
IF @SetNocountOn = 1
SET NOCOUNT ON
EXEC SP_EXECUTESQL
@SQL,
N'@ErrorCount INT OUTPUT',
@ErrorCount OUTPUT
END
GO
EXEC sys.sp_ms_marksystemobject sp_ExecBatchTerminated
GO