How to use 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

SQL Server 2005 Forums

Transact-SQL (2005)

How to use Try Catch

Author

Topic

imdad
Starting Member

1 Post

Posted - 2009-05-14 : 02:12:23

Hi all,

I have created the stored procedure which contains exception handling block like try/catch.
The following bit of code in my stored procedure

...
BEGIN TRY
declare @s nvarchar(max)
set @s = '[12_cross_SOURCE].[SOURCE].[dbo].sp_executesql N''DELETE FROM CROSS_PARENT WHERE PID IN ( Select PID From [12_cross_APPLICATION].[APPLICATION].[dbo].T_CROSS_PARENT_22)'''
exec sp_executesql @s
END TRY
BEGIN CATCH
DECLARE @ErrMsg nvarchar(4000)
SELECT @ErrMsg = ERROR_MESSAGE()
PRINT 'Error occured:- ' + @ErrMsg
END CATCH
...

Now, I want to catch the exception using try/catch but could not be cached.

When I execute only the following line of code without stored procedure then it will give me an error message like
Msg 547, Level 16, State 1, Line 1
Error No:- 547
Error Msg:-
"The DELETE statement conflicted with the REFERENCE constraint "FK_CROSS_CHILD1_CROSS_PARENT". The conflict occurred in database "SOURCE", table "dbo.CROSS_CHILD1", column 'CID'"

BEGIN TRY
declare @s nvarchar(max)
set @s = '[12_cross_SOURCE].[SOURCE].[dbo].sp_executesql N''DELETE FROM CROSS_PARENT WHERE PID IN ( Select PID From [12_cross_APPLICATION].[APPLICATION].[dbo].T_CROSS_PARENT_22)'''
exec sp_executesql @s
END TRY
BEGIN CATCH
DECLARE @ErrMsg nvarchar(4000)
SELECT @ErrMsg = ERROR_MESSAGE()
PRINT 'Error occured:- ' + @ErrMsg
END CATCH

I am executing the statement using linked server like [12_cross_SOURCE] and [12_cross_APPLICATION].

Please let me know how I can catch the exception for 547 in stored procedure.

Please help me out to handle exception.

Thanks in advance
Imdad

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-05-14 : 02:16:40

I think the problem may be around calling stored procedures from within a Try + Catch

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-14 : 10:38:46

http://www.sommarskog.se/error-handling-II.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources