November 25, 2018 at 9:51 pm
Hi I'm trying to code a similar Stored procedure as below. Below SP is from the Microsoft website, the only change is i have a insert statement instead of delete and i wanted to send an email if @@error<>0 within the Begin end after If @@error<>0 it should go to email part and send email saying insert failed. But it just fails right after insert and doesnt end up in the email block.
Does @@error work with DML operations only? Delete or update?
Thanks in advance...
USE AdventureWorks2012; GO -- Drop the procedure if it already exists. IF OBJECT_ID(N'HumanResources.usp_DeleteCandidate', N'P') IS NOT NULL
DROP PROCEDURE HumanResources.usp_DeleteCandidate; GO -- Create the procedure.
CREATE PROCEDURE HumanResources.usp_DeleteCandidate
( @CandidateID INT )
AS
-- Execute the DELETE statement.
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = @CandidateID;
-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
-- This is where i want to send email...
PRINT N'An error occurred deleting the candidate information.';
RETURN 99;
END
ELSE
BEGIN -- Return 0 to the calling program to indicate success.
PRINT N'The job candidate has been deleted.';
RETURN 0;
END; GO
November 26, 2018 at 5:22 am
I suggest you do this using TRY/CATCH:BEGIN TRY
--Perform your INSERT here
END TRY
BEGIN CATCH
--Send your e-mail here
END CATCH
What sort of failures are you expecting to handle in this way? PK errors? Wrong column errors?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 26, 2018 at 9:25 am
It would also help to post the full error message you are getting when you run the code.
November 26, 2018 at 11:39 am
Certain errors cause SQL to exit the batch, no matter what the code says.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply