Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Handling error message Expand / Collapse
Author
Message
Posted Monday, July 15, 2013 5:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:50 PM
Points: 1,867, Visits: 2,275
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 LOG 

Here 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/
Post #1473581
Posted Monday, July 15, 2013 6:05 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, April 18, 2014 10:37 PM
Points: 3,306, Visits: 2,351
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.



Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1473607
Posted Monday, July 15, 2013 6:50 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:49 AM
Points: 739, Visits: 2,470
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 LOG 


I 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.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1473629
Posted Monday, July 15, 2013 6:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:50 PM
Points: 1,867, Visits: 2,275
Thanks Wegner for your suggestion...


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1473632
Posted Monday, July 15, 2013 6:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:50 PM
Points: 1,867, Visits: 2,275
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/
Post #1473633
Posted Monday, July 15, 2013 8:21 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, April 18, 2014 10:37 PM
Points: 3,306, Visits: 2,351
The RAISERROR simply throws the error. To terminate processing in a stored procedure, you should fire a RETURN.


Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1473678
Posted Monday, July 15, 2013 11:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:50 PM
Points: 1,867, Visits: 2,275
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/
Post #1473983
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse