Results 1 to 6 of 6
  1. #1
    Join Date
    May 2007
    Posts
    2

    Unanswered: sp_executesql and error handling

    Hi,

    I need to capture the error code while using sp_executesql

    insert into #temp1
    exec @status=sp_executesql @actn_id1

    This @actn_id1 contains the proc name and the parameters. The problem now is when I get an error its not captured in the @status column, it always 0 and also tried @@error and was also 0.


    Server: Msg 55118, Level 16, State 1, Procedure S_START_LUW, Line 383
    info_upd_id =530129179546!, is using current subj_id.

    This is a user defined error captured using raiserror inside a sub proc.

    Can I capture this error number in the status column?

    Thanks in advance for the help

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Easiest would be to pass the error back as an output variable.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2007
    Posts
    2
    Thanks, But these SPs are being used by many other applications and i cant change it. I ran the stored procedure directly and the error code was captured in the @status column. Am i missing something while using SP_Executesql ?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can pass the error in an output variable as well as a return code. Nothing needs to change in the other code.

    Sorry - I don't throw errors in this way - I prefer to use output variables so I don't know of particular gotchas for this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2007
    Posts
    4
    You can use Try Catch block

    DECLARE @sql NVARCHAR(128)
    SET @sql = 'SELECT * FROM Sample'

    BEGIN TRY
    EXEC sp_executesql @sql
    END TRY

    BEGIN CATCH
    SELECT ERROR_MESSAGE(), ERROR_NUMBER()
    END CATCH
    GO

    i assume that you will be using SQL Server 2005.

    Hope this will help you

  6. #6
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by s_dev
    The problem now is when I get an error its not captured in the @status column, it always 0 and also tried @@error and was also 0.
    Keep in mind that the error code you are catching is the error code that refers to the processing status of the sp_executesql command, NOT whatever that command is executing. That means that unless the sp_executesql itself fails, you are going to see the return code of zero (success) regardless of what the stored proc returns. That's why you need to return it using an output variable or some other type get-around-the-normal-processing methodology.

    edit....removed raiserror references...D'oh!!!
    Last edited by TallCowboy0614; 05-29-07 at 16:31.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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