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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply