Handling error message
-
Hi, while writing script I have to check that whether input value is correct or not and then display the error message for that...
What I did is use RAISERROR
RAISERROR('Invalid Amount',20,1) WITH LOGHere is some part of my code:
IF NOT EXISTS (SELECT (a.VoucherNo)FROM GV_Voucher a
WHERE a.TransactionID = @TransactionID AND a.VoucherNo = @FirstVoucherNo )
BEGIN
SET @ErrorMessage = @ErrorMessage + ' ' + 'First ' + @message2
RAISERROR('User Define: Invalid First Voucher No |', 20, 1) WITH LOG
END
IF NOT EXISTS (SELECT (a.VoucherNo) FROM GV_Voucher a WHERE a.TransactionID = @TransactionID AND a.VoucherNo = @LastVoucherNo)
BEGIN
RAISERROR('User Define: Invalid Last Voucher No |', 20, 1) WITH LOG
SET @ErrorMessage = @ErrorMessage + ' ' +'Last' + @message2
END
If I handle error in this manner will it make any performance effect on my procedure.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
I use RAISERROR myself and it works pretty well. I usually use this approach.
IF NOT EXISTS (SELECT (a.VoucherNo) FROM GV_Voucher a WHERE a.TransactionID = @TransactionID AND a.VoucherNo = @LastVoucherNo)BEGIN
RAISERROR('User Define: Invalid Last Voucher No |', 20, 1)
RETURN;
END;
What I'm after is to check the value of a parameter or make sure rows exist where they need to and throw an error and terminate if there's a problem. It works very well. Just don't forget to add in a RETURN statement to terminate execution if there's an error that won't allow the rest of the procedure to run.
-
kapil_kk (7/15/2013)
Hi, while writing script I have to check that whether input value is correct or not and then display the error message for that...What I did is use RAISERROR
RAISERROR('Invalid Amount',20,1) WITH LOGI see you are using a severity level of 20, which seems a bit excessive.
Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.
-
Thanks Wegner for your suggestion...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Actually first I tried with Batch Abort but it did nt work for me.......
SET XACT_ABORT On;select 'yes'
RAISERROR('yes',20,1)
select 'yes'
RAISERROR('NO',16,1)
SELECT 'NO'
When error raises then also all select statements gets executed so I choose RAISERROR instead of that............
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
The RAISERROR simply throws the error. To terminate processing in a stored procedure, you should fire a RETURN.
-
Ed Wagner (7/15/2013)
The RAISERROR simply throws the error. To terminate processing in a stored procedure, you should fire a RETURN.yes, that why now am insteady of choosing severity 20 will use 16 and put RETURN statement with that and its working correctly.....:-)
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply