try catch - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

try catch

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-18 : 18:48:55

I am trying to write a exception which for any reason the code fails it should go to catch block and handle there, but the below code is giving syntax err(intentionally added to test) at begin try .. end try code.

I want the control to transfer to catch blk for any error, table b does not exists


BEGIN TRY
select '' into a from b
END TRY

BEGIN CATCH
	DECLARE @User_ERRMSG NVARCHAR(MAX)
	SET @User_ERRMSG=''
	SET @User_ERRMSG = 'ERROR MESSAGE:  Query Failed please check the existance of data files one or more of these files might be missing '
	SELECT 	ERROR_NUMBER()  AS [System Error Number]
		   ,ERROR_MESSAGE() AS [System Error Message]
		   ,ERROR_LINE()  AS [Error Line]
		   ,@User_ERRMSG AS [User Defined Error Message]
END CATCH

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-18 : 19:04:53

let me know if anymore info is needed

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-18 : 19:34:05

this is from BOL

TRY…CATCH constructs do not trap the following conditions:

Warnings or informational messages that have a severity of 10 or lower.

Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.

Attentions, such as client-interrupt requests or broken client connections.

When the session is ended by a system administrator by using the KILL statement.

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

Compile errors, such as syntax errors, that prevent a batch from running.

Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-18 : 19:46:02

Thanks Visakh,

Is there any way or work around to add this exceptions. comparatively in Oracle we have "when others" exception which catchs any type of exception. do we have any such in SQL Server

-Neil

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-18 : 20:00:14

You can't "work around" a compilation error, if the code can't compile successfully there's nothing for the error handling to handle.

If you have specific objects that you depend on you can test for their existence and then handle the "error" accordingly:

IF OBJECT_ID('b') IS NULL BEGIN
-- code to create table B or raise an error
END

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-08-19 : 14:36:23

Thanks Robvolk,

The RAISEERROR was working .. but however when it encountered the statement RAISERROR it also going to the next statement and executing it and giving syntax err which is there.

I want to to directly go to the try catch block and exit from there.. when ever it encounter RAISERROR

-Neil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources