Error Reporting in Stored Procedures

Posted on 2005-04-06
Last Modified: 2008-03-06
I have a master stored procedure,sp_overall_proc.This executes 2 stored procs,sp_proc1 and sp_proc2.

All that these sp_proc1 and sp_proc2 do is populate tables,based on certain conditions.
Inside of each,I have checked for errors and assigned to local variables and done simple print messages.Is there a way I can pass these error codes to the overall stored procedure ,such that error reporting is done by the sp_overall_proc rather than the internal stored procedures?

Question by:aswam1975
    LVL 8

    Accepted Solution

    Yes, you can return numeric code from a stored procedure. Assume you have a local variable @errCode in your stored proc, then you can return from that stored procedure with

    return (@errCode)

    Make sure you have the parentheses around the variable. You would call this from the main stored procedure like this:

    execute @returnedError = sp_proc2 ...

    Author Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now