问题描述
我有点困惑为什么我似乎无法通过下面的语句获得插入行的新身份".SCOPE_IDENTITY() 只返回 null.
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)
任何帮助将不胜感激.
现在我使用这样的解决方法.
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
推荐答案
您可以尝试为 output 子句使用表变量,从而允许您显式插入 FaildMessages代码>:
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)