W naszej aplikacji, gdy rekord jest pobierany po wybraniu SQL Server musi zaktualizować rekord. Robi to, zmieniając status (jest to jedna z kolumna) z żądanego do zakończenia

Używam SET XACT_ABORT ON, czy mogę zaktualizować rekord tuż po wybraniu go?

ALTER PROCEDURE [dbo].[GetOrderExportJobForProcess]
AS
BEGIN
    DECLARE @Msg VARCHAR(1000), @ErrSev INT, @count INT

    BEGIN TRY
        SET NOCOUNT ON
        SET XACT_ABORT ON -- <==

        BEGIN
            SELECT TOP 1 JobId, ProfileId, AffinityId, TrackId, StoreId, Request, [Status], Note, CreatedOn, UpdatedOn,ReqVersion
            FROM OrderExportJob WITH(NOLOCK)
            WHERE IsActive = 1 AND [Status] = 'Requested'
            ORDER BY CreatedOn DESC
        END
    END TRY

    BEGIN CATCH
        SET @ErrSev = Error_Severity()
        SET @Msg = 'Error #' + Cast(Error_Number() AS VARCHAR(10)) + ' occured!' + CHAR(13) + Error_Message() + CHAR(13) + 'Severity: ' + Cast(Error_Severity() AS VARCHAR(10)) + '  ' + 'State: ' + Cast(Error_State() AS VARCHAR(10)) + CHAR(13) + 'In ' + CASE WHEN Error_Procedure() IS NULL THEN 'GetOrderExportJobForProcess' ELSE 'GetOrderExportJobForProcess' + Error_Procedure() END + '  ' + 'On Line: ' + Cast(Error_Line() AS VARCHAR(10))

        RAISERROR (@Msg, @ErrSev, 1)
    END CATCH
END
0
sks 5 grudzień 2019, 05:27

1 odpowiedź

Najlepsza odpowiedź

Wypróbuj tę odpowiedź.

ALTER PROCEDURE [dbo].[GetOrderExportJobForProcess]
AS
BEGIN
    DECLARE @Msg VARCHAR(1000), @ErrSev INT, @count INT

    BEGIN TRY
        SET NOCOUNT ON
        **SET XACT_ABORT ON**

            SELECT TOP 1 JobId, ProfileId, AffinityId, TrackId, StoreId, Request, [Status], Note, CreatedOn, UpdatedOn,ReqVersion
            INTO #temp
            FROM OrderExportJob WITH(NOLOCK)
            WHERE IsActive = 1 AND [Status] = 'Requested'
            ORDER BY CreatedOn DESC

            SELECT * FROM #Temp

            UPDATE OrderExportJob
            SET YourColumn = UpdateValue
            WHERE JobId = (SELECT JobID FROM #Temp)

    END TRY

    BEGIN CATCH
        SET @ErrSev = Error_Severity()
        SET @Msg = 'Error #' + Cast(Error_Number() AS VARCHAR(10)) + ' occured!' + CHAR(13) + Error_Message() + CHAR(13) + 'Severity: ' + Cast(Error_Severity() AS VARCHAR(10)) + '  ' + 'State: ' + Cast(Error_State() AS VARCHAR(10)) + CHAR(13) + 'In ' + CASE WHEN Error_Procedure() IS NULL THEN 'GetOrderExportJobForProcess' ELSE 'GetOrderExportJobForProcess' + Error_Procedure() END + '  ' + 'On Line: ' + Cast(Error_Line() AS VARCHAR(10))

        RAISERROR (@Msg, @ErrSev, 1)
    END CATCH
END
1
DineshDB 5 grudzień 2019, 04:03