SQL Server OUTPUT clause

output-clause, scope-identity, sql-server, t-sql

Solution

You might try to use a table variable for your `output` clause, thus allowing you to explicitly insert into `FaildMessages`:

declare     @WorkRequestQueueID int
declare @LastException nvarchar(MAX)
set @WorkRequestQueueID = 1
set @LastException = 'test'

set nocount off

    -- Declare a table variable to capture output
    DECLARE @output TABLE (
        MessageEnvelope VARCHAR(50),    -- Guessing at datatypes
        Attempts INT,                   -- Guessing at datatypes
        WorkItemReceived_UTC DATETIME   -- Guessing at datatypes
    )

    -- Run the deletion with output
    DELETE dbo.WorkRequestQueue
    OUTPUT
        DELETED.MessageEnvelope,
        DELETED.Attempts,
        DELETED.WorkItemReceived_UTC
    -- Use the table var
    INTO @output
    FROM dbo.WorkRequestQueue
    WHERE
        WorkRequestQueue.ID = @WorkRequestQueueID

    -- Explicitly insert
    INSERT
    INTO dbo.FaildMessages
    SELECT
        MessageEnvelope,
        Attempts,
        @LastException,
        GetUtcdate(), -- WorkItemPoisened datetime
        WorkItemReceived_UTC
    FROM @output


IF @@ROWCOUNT = 0 
  RAISERROR ('Record not found', 16, 1)


SELECT Cast(SCOPE_IDENTITY() as int)

Problem

I am a little stuck with why I can not seem to get the 'new identity' of the inserted row with the statement below. `SCOPE_IDENTITY()` just returns null. ``` declare @WorkRequestQueueID int declare @LastException nvarchar(MAX) set @WorkRequestQueueID = 1 set @LastException = 'test' set nocount off DELETE dbo.WorkRequestQueue OUTPUT DELETED.MessageEnvelope, DELETED.Attempts, @LastException, GetUtcdate(), -- WorkItemPoisened datetime DELETED.WorkItemReceived_UTC INTO dbo.FaildMessages FROM dbo.WorkRequestQueue WHERE WorkRequestQueue.ID = @WorkRequestQueueID IF @@ROWCOUNT = 0 RAISERROR ('Record not found', 16, 1) SELECT Cast(SCOPE_IDENTITY() as int) ``` Any assistance would be most appreciated. For now I use a workaround this like so. ``` declare @WorkRequestQueueID int declare @LastException nvarchar(MAX) set @WorkRequestQueueID = 7 set @LastException = 'test' set nocount on set xact_abort on DECLARE @Failed TABLE ( MessageEnvelope xml, Attempts smallint, LastException nvarchar(max), WorkItemPoisened_UTC datetime, WorkItemReceived_UTC datetime ) BEGIN TRAN DELETE dbo.WorkRequestQueue OUTPUT DELETED.MessageEnvelope, DELETED.Attempts, @LastException, GetUtcdate(), -- WorkItemPoisened datetime DELETED.WorkItemReceived_UTC INTO @Failed FROM dbo.WorkRequestQueue WHERE WorkRequestQueue.ID = @WorkRequestQueueID IF @@ROWCOUNT = 0 BEGIN RAISERROR ('Record not found', 16, 1) Rollback END ELSE BEGIN insert into dbo.FaildMessages select * from @Failed COMMIT TRAN SELECT Cast(SCOPE_IDENTITY() as int) END ```

Original source