February 4, 2009 at 1:09 am
Hi,
I want to use try-catch block against any user defined condition. say I am ising an IF block to satisfy some conditions . If it will dissatisfy, then I want to go to CATCH block. Is it possible?
BEGIN TRY
IF (@variable between 1 AND 8) --condition as per client emand) --
error produced
END TRY
BEGIN CATCH
END CATCH
February 4, 2009 at 1:24 am
[font="Verdana"]What if you will handle FALSE in Else part? Why you need this situation to be handle in Catch part?
Mahesh[/font]
MH-09-AM-8694
February 4, 2009 at 1:41 am
You can use raiserror to raise your own error. It will behave just like any runtime error:
use tempdb
go
create proc demo (@variable int)
as
BEGIN TRY
IF (@variable between 1 AND 8) --condition as per client emand) --
raiserror(50001,16,1)
else
select 'there was no error'
END TRY
BEGIN CATCH
select 'oops there was an error'
END CATCH
exec Demo 3 --should se an error
go
exec Demo 9 --should be successfull
go
drop proc demo
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 4, 2009 at 3:34 am
Hi Adi,
Thanks.
It solves my problem.
February 4, 2009 at 3:35 am
Hi Mahesh ,
i want to do the same through TRY_catch BLOCK.
Thanks for your help.
February 4, 2009 at 6:25 am
Hi, another question:
RAISERROR raising errors properly from SQL MS.
But from front end (it is in JAVA), no error message is showing. But the SP is working fine. It is giving the correct functionality. What may be the problem? (I have a very few knowledge in Java).
February 4, 2009 at 9:22 am
The catch block already handled the error, so it will not be sent farther to the calling application. You have few options. You can use the return value or output parameter to indicate to the calling application that there was some kind of an error. The other option is to run raiserror again in the catch block. This will cause an unhandled error and the java application will get an error message from SQL Server. I personally prefer the first way (return code or output parameter).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 4, 2009 at 11:30 pm
Hi adi,
I have checked the second method you have described. But its not throwing any error from JAVA frontend, though raising error from SQLMS.
What 2 do?
February 5, 2009 at 1:54 am
Could be that the Severity level of the error that you are raising is to low (you can read about the serverity levels in books on line).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 6, 2009 at 6:48 am
Thanks adi, I will try ur solution.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply