USE master
GO
IF OBJECT_ID('sp_GetError') IS NULL EXEC ('CREATE PROCEDURE sp_GetError AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_GetError
@ErrorCount INT = NULL OUTPUT,
@ErrorTable VARCHAR(128) = NULL OUTPUT,
@SelectError BIT = 0,
@PrintError BIT = 0,
@FillErrorList BIT = 0,
@ErrorList VARCHAR(MAX) = NULL OUTPUT
AS
DECLARE @SQL NVARCHAR(MAX)
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET @ErrorTable = CAST(CONTEXT_INFO() AS VARCHAR(128))
IF NOT EXISTS ( SELECT *
FROM tempdb.sys.tables (NOLOCK)
WHERE name = @ErrorTable )
BEGIN
SELECT @ErrorCount = 0,
@ErrorTable = NULL,
@ErrorList = NULL
RETURN
END
SET @SQL = 'SELECT @ErrorCount = COUNT(*) FROM ' + @ErrorTable + '
' + CASE WHEN @PrintError = 1
OR @FillErrorList = 1
THEN 'IF @ErrorCount > 0
SELECT @ErrorList = Util.dbo.StringConcat(''-- Id: '' + CAST(Id AS VARCHAR) + '', ErrorNumber: '' + CAST(ErrorNumber AS VARCHAR) + '', ErrorSeverity: ''
+ CAST(Severity AS VARCHAR) + '', DateAdded: '' + CAST(DateAdded AS VARCHAR) + ''
---- ErrorMessage:
'' + ISNULL(text, ''NULL'') + ''
---- Inbutbuffer:
'' + ISNULL(INPUTBUFFER, ''NULL''), ''
'')
FROM ' + @ErrorTable
+ ' e (NOLOCK)
LEFT OUTER JOIN sys.messages (NOLOCK) m ON e.ErrorNumber = m.message_id AND m.language_id = (SELECT msglangid FROM sys.syslanguages (NOLOCK) WHERE langid = @@langid)
' ELSE ''
END
+ CASE WHEN @SelectError = 1
THEN 'IF @ErrorCount > 0
SELECT e.Id, e.ErrorNumber, m.Severity as ErrorSeverity, m.text as ErrorMessage, CASE WHEN e.InputBuffer <> '''' THEN (SELECT 1 AS tag, NULL AS parent, CHAR (13) + e.InputBuffer + CHAR (13) AS [Q!1!Q!CDATA]
FOR XML EXPLICIT,
TYPE) END as InputBufferXML, e.InputBuffer, e.DateAdded
FROM ' + @ErrorTable
+ ' e (NOLOCK)
LEFT OUTER JOIN sys.messages (NOLOCK) m ON e.ErrorNumber = m.message_id AND m.language_id = (SELECT msglangid FROM sys.syslanguages (NOLOCK) WHERE langid = @@langid)
' ELSE ''
END
EXEC sys.sp_executesql
@SQL,
N'@ErrorCount INT OUTPUT, @ErrorList VARCHAR(MAX) OUTPUT',
@ErrorCount OUTPUT,
@ErrorList OUTPUT
IF @PrintError = 1
EXEC Util.dbo.PrintLargeText
@ErrorList
GO
EXEC sys.sp_ms_marksystemobject sp_GetError
GO