Results 1 to 2 of 2
  1. #1
    Join Date
    May 2009
    Posts
    1

    Unanswered: How to use Try Catch

    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

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Please use code tags - they make things much more readable.
    Code:
    BEGIN TRY
    
        DELETE    CROSS_PARENT
        FROM    CROSS_PARENT AS del
        WHERE    EXISTS    (SELECT NULL 
                        FROM [12_cross_APPLICATION].[APPLICATION].[dbo].T_CROSS_PARENT_22 AS match 
                        WHERE match.PID = del.PID)
    
    END TRY
    BEGIN CATCH
    
        PRINT    'Error occured:- '
        PRINT    ERROR_MESSAGE()
    
    END CATCH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •