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.

Answer 1

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.

Answer 2

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

Answer 3

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