How to check a delete / upadate operation completed successfully, using stored procedure ?

Question

Say we have stored procedure(s) performing simple operations

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 above procedure, the retuned id is an indication of successful operation.

Consider this

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 record (author) was succesfully removed, if we use above procedure ?

• Same with an update operation ?

thanks

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.

Answer 3

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