SQL Server OUTPUT-Klausel
Ich bin ein wenig festgefahren, warum ich die 'neue Identität' der eingefügten Zeile mit der folgenden Aussage nicht zu bekommen scheinen kann.SCOPE_IDENTITY()
Gibt einfach null zurück.
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)
Jede Hilfe wäre sehr dankbar.
Im Moment verwende ich einen Workaround wie diesen.
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