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?

Answer 1

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.

Answer 2

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