TRY/CATCH. What if record does not exist?

[willydude]

I’m trying to use the TRY/CATCH method when deleteing a record. If the record exists, it works. But if the record does not exist? The following doesn’t tell me that the record does not exist and I do not receive any error message. Please note the attempt with the IF/ELSE.

BEGIN TRY
   BEGIN TRANSACTION    

/*
IF EXISTS(SELECT * FROM tbl_Admin_BatchNumbers
 where BatchNumber = @BatchNumber)
  BEGIN
*/

Delete from tbl_Admin_BatchNumbers
  where tbl_Admin_BatchNumbers.BatchNumber = 
    @BatchNumber

--If we reach here, success!
COMMIT
--end

/*
ELSE --Batch number does not exist. tell user. Rollback.

Begin
set @Message = 'Batch Number Does Not Exist.'
rollback
End
*/

END TRY

BEGIN CATCH
-- Whoops, there was an error
  IF @@TRANCOUNT > 0
     ROLLBACK

-- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

--Keep the RAISERROR for ASP use
  RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

TIA.

Bill

 

[gmmastros]

The problem here is that there is no error when you attempt to delete a record that does not exist. Since there is no error, it will never go in to the catch block.

You'll need to use the Exists check that you have commented out.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[willydude]

GM,

I think I was confusing a SQL error with my @Message error of "Batch Number Does Not Exist."

SQL won't throw an error if there is no batch number. Correct?

Thanks.

Bill

 

[mrdenny]

Correct, SQL will not throw an error is no records are deleted.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[ca8msm]

Why would it throw an error? It simply deletes the rows where a certain criteria is matched. If there happens to be 0, 1, or 1000 rows that match then it's still a valid statement.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[gmmastros]

That's correct.

Take a look at this...

Declare @Temp Table(Id Int)

Insert Into @Temp Values(1)

Delete From @Temp Where Id = 7

Select * From @Temp

Copy paste this to SQL Server Management Studio and run it. You'll see that there are no errors and nothing got deleted.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom