How to know TSQL Stored Procedure Update Executed

Question

How can I check if my TSQL stored procedure updated within the stored procedure in order to create a proper message?

Example:

ALTER PROCEDURE [dbo].[pUpdate]
            @id uniqueidentifier, 
            @status int,
            @message VARCHAR(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [database].[dbo].[user]
    SET status = @status
    WHERE Id = @id
END

IF (SUCCESSFUL)
BEGIN
    @message = 'Success!'
END

What are some possible ways to check if successful without using the parameters again?

This is what I currently use:

  SELECT COUNT(*)
    WHERE status = @status AND id = @id

Are there any other ways? I want to know for my knowledge and reference. Thanks.

score 22 · Accepted Answer · answered Oct 02 '13 at 19:54

22

Have you checked out @@ROWCOUNT? Might be what you're looking for (see this for details: http://technet.microsoft.com/en-us/library/ms187316.aspx). Basically it returns the number of rows affected by the last statement. I'd imagine if it were not "successful", it would be zero rows.

answered Oct 02 '13 at 19:54

rory.ap

34,009
10
83
174

1

+1, but you could also use `IF EXISTS (SELECT 1 FROM database.dbo.user WHERE Id = @id AND status = @status)` which is way better than `COUNT(*)` another method would be the `OUTPUT` clause to capture the data into a @temp table variable. – KM. Oct 02 '13 at 20:01
1

@roryap Yes, or if the @@ROWCOUNT <> [x] (with x being the number of values you want to update), you can throw an error, or rollback the transaction, giving you the power to specify exactly how many rows you want updated, if more than one. – Question3CPO Oct 02 '13 at 20:13

score 4 · Answer 2 · answered Oct 09 '13 at 10:44

ALTER PROCEDURE [dbo].[pUpdate]
            @id uniqueidentifier, 
            @status int,
            @message VARCHAR(100) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [database].[dbo].[user]
    SET status = @status
    WHERE Id = @id
END

IF (@@ROWCOUNT > 0)
BEGIN
     @message = 'Success!'
END
ELSE 
BEGIN
    @message = 'Not success!'
END

score 1 · Answer 3 · answered Oct 02 '13 at 19:54

You can use a try catch block and log the success or failure to a table.

BEGIN TRY
    BEGIN TRANSACTION
    -- Add Your Code Here
    -- Log Success to a log table
    COMMIT
END TRY
BEGIN CATCH
    -- Log failure to a log table
    ROLLBACK
END CATCH

G21 · Answer 4 · 2013-10-02T20:15:06.010

0

I would use @@ERROR system variable to check whether the last sentence was successfull (error # = 0) or not (error # > 0 ):

USE Database;
GO

BEGIN
    UPDATE TableName
    SET ColumnA = 4
    WHERE ColumnB = 1;
END

IF (@@ERROR = 0)
BEGIN
    PRINT N'Successfull Update';
    GO
END

You can go deeper into Microsoft MSDN here: http://technet.microsoft.com/es-es/library/ms188790.aspx

edited Oct 02 '13 at 20:15

answered Oct 02 '13 at 20:08

G21

1,297
2
18
39

score 0 · Answer 5 · answered Jul 02 '22 at 07:05

0

 ALTER PROCEDURE [dbo].[pUpdate]
                @id uniqueidentifier, 
                @status int,
                @message VARCHAR(100) OUTPUT
    AS
    BEGIN
        SET NOCOUNT ON;
    
        UPDATE [database].[dbo].[user]
        SET status = @status
        WHERE Id = @id
    END
    
    IF (@@ROWCOUNT > 0)
    BEGIN
        SELECT @message = 'Success!'
    END
    ELSE 
    BEGIN
       SELECT @message = 'Not success!'
    END

answered Jul 02 '22 at 07:05

Ayyan Khan

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 03 '22 at 00:34

How to know TSQL Stored Procedure Update Executed

5 Answers5