-- Create the schema hmem (= Handling Multiple Error Messages).
IF schema_id('hmem') IS NULL  
   EXEC('CREATE SCHEMA hmem')
go
-- Returns the name for an error event session for the current spid.
IF object_id('hmem.xe_event_name') IS NULL
   EXEC('CREATE FUNCTION hmem.xe_event_name() RETURNS sysname AS BEGIN RETURN '''' END')
go
ALTER FUNCTION hmem.xe_event_name() RETURNS sysname AS
BEGIN
   RETURN concat('error_handling_spid_', @@spid)
END
go
-- Drops any current event session if it exists. 
IF object_id('hmem.drop_error_event_session') IS NULL
   EXEC('CREATE PROCEDURE hmem.drop_error_event_session AS PRINT 1')
go
ALTER PROCEDURE hmem.drop_error_event_session AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
   DECLARE @session_name sysname = hmem.xe_event_name(),
           @sql nvarchar(MAX)

   -- Check if session already exists. In this case drop it and re-create.
   IF EXISTS (SELECT * 
              FROM   sys.server_event_sessions 
              WHERE  name = @session_name)
   BEGIN
      SELECT @sql = 'DROP EVENT SESSION ' + quotename(@session_name) + 
                    'ON SERVER'
      EXEC(@sql)
   END
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ; THROW
END CATCH
go
-- Creates and starts an event session filtered for the current spid
-- to capture exceptions.
IF object_id('hmem.start_error_event_session') IS NULL
   EXEC ('CREATE PROCEDURE hmem.start_error_event_session AS PRINT 1')
go
ALTER PROCEDURE hmem.start_error_event_session AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
   DECLARE @session_name sysname = hmem.xe_event_name(),
           @sql nvarchar(MAX)

   -- Drop session if exists and recreate.
   EXEC hmem.drop_error_event_session

   -- Create the event session.
   SELECT @sql = 'CREATE EVENT SESSION ' + quotename(@session_name) + '
                 ON SERVER
                 ADD EVENT sqlserver.error_reported (
                     WHERE severity >= 11
                       AND sqlserver.session_id = ' + convert(varchar(10), @@spid) + ')
                 ADD TARGET package0.ring_buffer 
                 WITH (
                      EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
                      MAX_MEMORY = 4096 KB,
                      MAX_DISPATCH_LATENCY = 1 SECONDS,
                      MAX_EVENT_SIZE = 0 KB,
                      MEMORY_PARTITION_MODE = NONE,
                      TRACK_CAUSALITY = OFF,
                      STARTUP_STATE = OFF
                 )
                 ALTER EVENT SESSION ' + quotename(@session_name) + '
                 ON SERVER STATE = START'
   -- PRINT @sql 
   EXEC(@sql)
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ; THROW
END CATCH
go
-- This procedure reads the error messages from the extended event sessions. If
-- you have create the temp table #hmem_errors, it will save the errors to this 
-- table. Depending on the @print_errors parameter, the procedure will also print
-- the messages. There are three chocies:
-- 0 = silent, 1 = print as informational msgs, 2 - raise as errors. Default is 0,
-- but if you haven't create the temp table, the procedure changes 0 to 1.
IF object_id('hmem.process_error_messages') IS NULL
   EXEC('CREATE PROCEDURE hmem.process_error_messages AS PRINT 1')
go
ALTER PROCEDURE hmem.process_error_messages 
       @print_errors tinyint = 0 AS  -- 0 = silent, 1 = As informational, 2 = As errors.
SET NOCOUNT ON
-- No TRY-CATCH, since it is to be executed from a CATCH handler.
DECLARE @session_name sysname = hmem.xe_event_name(),
        @sql          nvarchar(MAX)

-- If temp table does not exist, create it.
IF object_id('tempdb..#hmem_errors') IS NULL
BEGIN
   CREATE TABLE #hmem_errors (id       int            NOT NULL IDENTITY PRIMARY KEY,
                              errno    int            NOT NULL,
                              severity tinyint        NOT NULL,
                              state    int            NOT NULL,
                              errproc  sysname        NULL,
                              linenum  int            NULL,
                              msgtext  nvarchar(2048) NOT NULL,
                              -- errtime datetime2(3) NOT NULL DEFAULT sysdatetime()
   )

   -- In this case, it would be silly to run with @print_errors = 0
   IF @print_errors = 0 
      SELECT @print_errors = 1
END

-- Extended event sessions has latencies, so the information may not
-- be there immediately. Loop until we have data, but don't wait forever
-- (The event session is set up with a seconds latency.)
DECLARE @retrycount int = 10,
        @XEData     xml = NULL,
        @eventcount int = 0
WHILE @retrycount > 0 AND @eventcount = 0
BEGIN
   SELECT @XEData = CAST(xet.target_data AS xml)
   FROM   sys.dm_xe_session_targets AS xet
   JOIN   sys.dm_xe_sessions AS xe ON xe.address = xet.event_session_address
   WHERE  xe.name = @session_name

   -- If we did not get any event data, the session is not running. No use to
   -- continue.
   IF @XEData IS NULL
      BREAK

   SELECT @eventcount = @XEData.value('(/RingBufferTarget/@totalEventsProcessed)[1]', 'int')
   IF @eventcount > 0
      BREAK

   SET @retrycount -= 1
   WAITFOR DELAY '00:00:00.200'
END

-- Get current max id in table.
DECLARE @startid int 
SELECT @startid = isnull(MAX(id), 0) FROM #hmem_errors

-- If we have events, insert these into the temp table.
IF @eventcount > 0
BEGIN
   INSERT #hmem_errors(errno, severity, state, errproc, linenum, msgtext)
      SELECT E.e.value(N'(data[@name="error_number"]/value)[1]', 'int'),
             E.e.value(N'(data[@name="severity"]/value)[1]', 'tinyint'),
             E.e.value(N'(data[@name="state"]/value)[1]', 'int'),
             error_procedure(),
             error_line(),
             E.e.value(N'(data[@name="message"]/value)[1]', 'nvarchar(2048)')
      FROM   @XEData.nodes('/RingBufferTarget/event') AS E(e)
      -- We only want messages directed to user.
      WHERE  try_convert(binary(4), 
               E.e.value(N'(data[@name="destination"]/value)[1]', 'char(10)'), 1) & 2 = 2
END

-- If we have no messages from X-events, fall back to the error_xxx functions.
IF @startid = (SELECT isnull(MAX(id), 0) FROM #hmem_errors)
BEGIN
   INSERT #hmem_errors(errno, severity, state, errproc, linenum, msgtext)
      VALUES(error_number(), error_severity(), error_state(), 
             error_procedure(), error_line(), error_message())
END

-- Print the errors to the user, if requested.
IF @print_errors > 0
BEGIN
   DECLARE @cur      CURSOR,
           @errno    int,
           @severity tinyint,
           @state    int,
           @errproc  sysname,
           @linenum  int,
           @message  nvarchar(2048)

   SET @cur = CURSOR STATIC FOR
      SELECT errno, severity, state, errproc, linenum, msgtext
      FROM   #hmem_errors
      WHERE  id > @startid
      ORDER  BY id

   OPEN @cur

   WHILE 1 = 1
   BEGIN
      FETCH @cur INTO @errno, @severity, @state, @errproc, @linenum,
                      @message
      IF @@fetch_status <> 0
         BREAK

      -- Prepend other information to message.
      SELECT @message = concat('Msg ', @errno, 
                               ', Level ', @severity, 
                               ', State ', @state,
                               ', Procedure ' + @errproc,  -- +, since on NULL, we want nothing.
                               ', Line ', @linenum,
                               nchar(13), nchar(10), @message)
       
      IF @print_errors = 1
         SELECT @severity = 0

      -- Raise/Print the error, use NOWAIT.
      RAISERROR ('%s', @severity, @state, @message) WITH NOWAIT
   END
END

-- Stop and restart the event session to clear the ring buffer.
IF EXISTS (SELECT * 
           FROM   sys.server_event_sessions 
           WHERE  name = @session_name) 
BEGIN
   SELECT @sql = 'ALTER EVENT SESSION ' + quotename(@session_name) + 
                 ' ON SERVER STATE = STOP
                 ALTER EVENT SESSION ' + quotename(@session_name) + 
                 ' ON SERVER STATE = START'
   EXEC (@sql)
END