CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Jaiesh_bhai
Jaiesh_bhai

Reputation: 1814

How to know TSQL Stored Procedure Update Executed

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.

Upvotes: 9

Views: 29874

Answers (5)

Ayyan Khan
Ayyan Khan

Reputation: 1

 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

Upvotes: 0

The Hill Boy
The Hill Boy

Reputation: 162

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

Upvotes: 4

G21
G21

Reputation: 1337

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

Upvotes: 0

Farnam
Farnam

Reputation: 177

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

Upvotes: 1

rory.ap
rory.ap

Reputation: 35260

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.

Upvotes: 22

Related Questions