Предложение SQL Server OUTPUT
Я немного застрял с тем, почему я не могу получить "новая личность вставленной строки с заявлением ниже.SCOPE_IDENTITY()
просто возвращает ноль.
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)
Любая помощь будет наиболее ценной.
Сейчас я использую обходной путь, вот так.
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