Try and catch not working for stored procedure

Question

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.

Answer 1

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

Answer 2

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 :

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

PD: Are you sure you want to throw an exception when a row is not found? Usually Exceptions are reserved for an exceptional things such as I/O errors, bad params etc..

Additionally, you should take in a count that Exceptions costly so is not a good way of returning data.

I think you should check the number of affected rows.