Update a record after selecting the record in SQL Server

Question

In our application when the record is fetched after selecting it SQL Server needs to update the record. It does so by changing the status(this is one of the column) from requested to completed

I am using SET XACT_ABORT ON can I update the record just after selecting it?

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

What exactly is your question? You can put as many selects, updates, inserts, deletes as you like in a stored procedure. `SET XACT_ABORT ON` isn't relevant to that, it determines how SQL Server handles errors. — Dale K, Dec 05 '19 at 02:30
Hi Dale , Can I update a record just after selecting it. Do I need trigger or simply can I use update sql statement — sks, Dec 05 '19 at 02:33
Thanks @SKS, as I asked above, please [edit] that directly into your question so its clear to all who read it. Then go ahead and add an update statement to your SP. The only danger with doing that is if your service fails to process it and you've already updated the status. Normally one would change the status to "In Progress" and then only set it "Complete" when the service actually finishes process. — Dale K, Dec 05 '19 at 02:45
I want to know if trigger is required in this case or not. Can I update the record in the same begin and end block just after selecting — sks, Dec 05 '19 at 02:45
Thanks a lot Dale I got it. But I am thinking why it is not good to create a trigger for this — sks, Dec 05 '19 at 02:50
I can't see how you would create a trigger, let alone why you would want to. Triggers run when an insert/update/delete operation happens. You don't have an insert/update/delete operation so what would you trigger on? And you can directly write your update here and avoid the overhead of a trigger. — Dale K, Dec 05 '19 at 02:52
Sure Dale. Hi All, I had query regarding whether I can update a record just after selecting it in the same stored proc. Do I need a trigger to update the record. The answer is we can put a update statement just after selecting it in the same stored proc. I also was not clear about the proper use of Trigger so this was all cleared by Dale answer — sks, Dec 05 '19 at 03:07
Aside: The `NoLock` query hint may not be your friend. Why do you _think_ you need it? [https://stackoverflow.com/questions/210171/effect-of-nolock-hint-in-select-statements](https://stackoverflow.com/questions/210171/effect-of-nolock-hint-in-select-statements) may be of interest. — HABO, Dec 05 '19 at 03:28
Hi HABO,I went through the link I am using the NoLock hint for the same purpose. Thanks — sks, Dec 05 '19 at 03:38
@DineshDB Hi DineshDB I am still not able to write the update query to update the same row which I selected in my stored proc .Can you help me on this. if anyone can help me on this. — sks, Dec 05 '19 at 03:42
Yes jobId is unique . Its a primary key and type is uniqueidentifier — sks, Dec 05 '19 at 03:45
Simple a terrible approach. You use nolock when your goal is to update a row. That makes no sense and can cause other errors. Neither does it make any sense to catch any errors since your code only reraises the error - using the older style raiserror function rather than throw. That case expression in your error string building is pointless; you hard code the procedure name but also use error_procedure() in that building - which is redundant in 2 ways. Your approved answer uses an inefficient pattern for the update logic. And what happens when your first query does not find ANY rows? — SMor, Dec 05 '19 at 13:04

DineshDB · Accepted Answer · 2019-12-05T04:03:00.173

Try this answer.

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

@sks, You can use both. If you are deal with large amount of data then you go with specific column names, but here the temp table holding a single record only. That doesn't a matter here. — DineshDB, Dec 05 '19 at 03:58

Update a record after selecting the record in SQL Server

1 Answers1