How to check that a delete / update operation completed successfully when using a stored procedure?

Question

Say we have stored procedure(s) performing simple operations like this:

CREATE PROCEDURE [dbo].[AddNewAuthorReturnID]
(   
    @Author_Name VARCHAR(MAX),
    @Author_ID int OUTPUT
)
AS
    SET NOCOUNT OFF;
BEGIN
    INSERT INTO AUTHORS (@Author_Name)
    VALUES (@Author_Name)
    SET @Author_ID = SCOPE_IDENTITY()
    
    SELECT @Author_ID
 END

In the above procedure, the returned id is an indication of successful operation.

Consider the equivalent with DELETE.

CREATE PROCEDURE [dbo].[DeleteAuthor]
(       
    @Author_ID int 
)
AS
    SET NOCOUNT OFF;
BEGIN
    DELETE FROM AUTHORS 
    WHERE
    (Author_ID = @Author_ID)
END

How can we know the operation was successful and the AUTHORS record was succesfully removed if we use the above procedure ?
With an update operation?

Johnno Nolan · Accepted Answer · 2010-01-19T12:19:48.677

5

You could select @@rowcount

It will show you the rows affected.

e.g

CREATE PROCEDURE [dbo].[DeleteAuthor]
(       
    @Author_ID int 
)
AS
    SET NOCOUNT OFF;
BEGIN
 DELETE FROM AUTHORS 
 WHERE
 (Author_ID = @Author_ID)
 SELECT @@ROWCOUNT 
END

This can be applied to update too.

CREATE PROCEDURE [dbo].[UpdateAuthor]
(       
    @Author_ID int 
)
AS
    SET NOCOUNT OFF;
BEGIN
 UPDATE AUTHORS 
    SET AuthorName = 'John'
 WHERE
 (Author_ID = @Author_ID)
 SELECT @@ROWCOUNT 
END

Alternatively you could use @@Error and raise an error id @@rowcount > 1 (if you only wanted to update one row).

e.g

CREATE PROCEDURE [dbo].[DeleteAuthor]
(       
    @Author_ID int 
)
AS
    SET NOCOUNT OFF;
BEGIN
 DELETE FROM AUTHORS 
 WHERE
 (Author_ID = @Author_ID)

 IF @@ROWCOUNT <>1
 BEGIN
  RAISERROR ('An error occured',10,1)
  RETURN -1
 END
END

As Giorgi says this will be returned as a returncode.

edited Jan 19 '10 at 12:19

answered Jan 19 '10 at 12:09

Johnno Nolan

29,228
19
111
160

@John, What if the statement did not affect any rows but no error has occurred? – Giorgi Jan 19 '10 at 12:11
@asdi, you can use @@ROWCOUNT in the same way with update operation but I do not recommend using it because if the stored procedure executes without error but no rows are affected it will return 0 – Giorgi Jan 19 '10 at 12:15
What is the better approach then ? should we make a check after the operation than record is still there or not ? – Asad Jan 19 '10 at 12:21
@asdi, return @@ERROR from you stored procedure. – Giorgi Jan 19 '10 at 12:29

score 3 · Answer 2 · answered Jan 19 '10 at 12:09

3

You can to return @@ROWCOUNT to determine if your last statement affected any record.

answered Jan 19 '10 at 12:09

Rubens Farias

57,174
8
131
162

@Rubens, What if the statement did not affect any rows but no error has occurred? – Giorgi Jan 19 '10 at 12:13
You're right, @Giorgi but I would to expect update/delete statements to affect records on online applications; bottom line is: depends on what OP want to do – Rubens Farias Jan 19 '10 at 12:19

Giorgi · Answer 3 · 2010-01-19T12:30:09.937

1

You can return value from stored procedure using return statement. The @@ERROR variable is equal to zero if there was no error.

@@ERROR

CREATE PROCEDURE [dbo].[DeleteAuthor]
(       
    @Author_ID int 
)
AS
    SET NOCOUNT OFF;
BEGIN
 DELETE FROM AUTHORS 
 WHERE
 (Author_ID = @Author_ID)

 Return @@ERROR

END

edited Jan 19 '10 at 12:30

answered Jan 19 '10 at 12:09

Giorgi

30,270
13
89
125

This returns if there is no error but what if the user was expecting to delete a record and it wasn't there? – Johnno Nolan Jan 19 '10 at 12:47
This is not a good suggestion because even if no rows where found to be deleted, `@@ERROR` is still equal to `0`. `@@ERROR` will only be different from `0` if something horrendous went wrong with the server or the query itself, like a `DEADLOCK` or `TIMEOUT` or bad syntax or just when an exception is thrown. – Paul-Sebastian Manole Dec 22 '21 at 09:13

How to check that a delete / update operation completed successfully when using a stored procedure?

3 Answers3