Try and catch not working for stored procedure-CodePudding

I'm creating a procedure to delete a row from my plants Table. The try and catch statement was to delete it but if the plantID doesn't match any plant, catch the error and print the error message. However, the catch isn't working and I'm unsure what how to fix it.

CREATE PROC spDeletePlant

   @PlantID INT,
   @PlantCode INT,
   @PlantName VARCHAR(25),
   @Description VARCHAR(50),
   @ListPrice MONEY,
   @BatchID INT,
   @CategoryID INT
AS

BEGIN TRY

   DELETE FROM Plants
   WHERE PlantID = @PlantID
END TRY
BEGIN CATCH

   THROW 50001, 'PlantID is incorrect.', 1
END CATCH;

Forgive the formatting, I am new to this site.

CodePudding user response：

A query not matching any rows is not an error - when you run the delete statement on its own, you don't see an error raised.

If you want to automatically throw an error if no rows are deleted you can simply do:

DELETE FROM Plants
WHERE PlantID = @PlantID

IF @@Rowcount = 0
BEGIN
   THROW 50001, 'PlantID is incorrect.', 1
END

CodePudding user response：

Your code is working but is not doing what you expect. This is because delete statement doesn't not throw any error in case row is not found.

What you can do is :

Do a previous select to look for the register being deleted, otherwise throw an error.
Check the affected rows variables. Which DB are you using? In MSSQL for example you can check @@ROWCOUNT