Returning Info from Stored Procedures

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720447

    Comments posted to this topic are about the item Returning Info from Stored Procedures

  • Carlo Romagnano

    SSC-Insane

    Points: 21987

    5

    Also with PRINT and RAISERROR

  • Rune Bivrin

    SSCertifiable

    Points: 7829

    Carlo Romagnano wrote:

    5

    Also with PRINT and RAISERROR

    By that reasoning you should also include THROW, as well as modifying data in tables (particularly temp tables created by the caller).

    Personally, I would not consider PRINT, RAISERROR, and THROW ways to return data. Using it that way I would consider an anti-pattern to be pounced upon.


    Just because you're right doesn't mean everybody else is wrong.

  • Carlo Romagnano

    SSC-Insane

    Points: 21987

    Rune Bivrin wrote:

    Carlo Romagnano wrote:

    5

    Also with PRINT and RAISERROR

    By that reasoning you should also include THROW, as well as modifying data in tables (particularly temp tables created by the caller).

    Personally, I would not consider PRINT, RAISERROR, and THROW ways to return data. Using it that way I would consider an anti-pattern to be pounced upon.

    PRINT and RAISERROR return "info" as the question asked.

    THROW doesn't return any info or data.

    INSERT/DELETE/UPDATE return info about the number of rows affected if SET NOCOUNT is OFF

    The question is about "Returning Info" from stored procs

  • Rune Bivrin

    SSCertifiable

    Points: 7829

    Carlo Romagnano wrote:

    Rune Bivrin wrote:

    Carlo Romagnano wrote:

    5

    Also with PRINT and RAISERROR

    By that reasoning you should also include THROW, as well as modifying data in tables (particularly temp tables created by the caller).

    Personally, I would not consider PRINT, RAISERROR, and THROW ways to return data. Using it that way I would consider an anti-pattern to be pounced upon.

    PRINT and RAISERROR return "info" as the question asked.

    THROW doesn't return any info or data.

    INSERT/DELETE/UPDATE return info about the number of rows affected if SET NOCOUNT is OFF

    The question is about "Returning Info" from stored procs

    Well, to retrieve the "info" returned by PRINT you need to be outside of SQL Server, as I'm not aware of a way to capture the argument from within SQL code. It is equivalent to a RAISERROR with severity 10, if memory serves me.

    To say that THROW doesn't return any info or data, while maintaining that RAISERROR does shows there's a bit of reading to be done, since THROW pretty much supplants RAISERROR. The difference is mainly that THROW doesn't allow you to specify any severity, leaving it at 16, which ensures that you can catch it in a TRY ... CATCH block.

    I suppose the question could have included the caveat that it is about returning info from a stored procedure to calling SQL code, rather than to the users in a tool such as Management Studio.


    Just because you're right doesn't mean everybody else is wrong.

  • BillLudlow

    SSCertifiable

    Points: 6553

    How about outputing data to a table?

  • Carlo Romagnano

    SSC-Insane

    Points: 21987

    Rune Bivrin wrote:

    Carlo Romagnano wrote:

    Rune Bivrin wrote:

    Carlo Romagnano wrote:

    5

    Also with PRINT and RAISERROR

    By that reasoning you should also include THROW, as well as modifying data in tables (particularly temp tables created by the caller).

    Personally, I would not consider PRINT, RAISERROR, and THROW ways to return data. Using it that way I would consider an anti-pattern to be pounced upon.

    PRINT and RAISERROR return "info" as the question asked.

    THROW doesn't return any info or data.

    INSERT/DELETE/UPDATE return info about the number of rows affected if SET NOCOUNT is OFF

    The question is about "Returning Info" from stored procs

    Well, to retrieve the "info" returned by PRINT you need to be outside of SQL Server, as I'm not aware of a way to capture the argument from within SQL code. It is equivalent to a RAISERROR with severity 10, if memory serves me.

    To say that THROW doesn't return any info or data, while maintaining that RAISERROR does shows there's a bit of reading to be done, since THROW pretty much supplants RAISERROR. The difference is mainly that THROW doesn't allow you to specify any severity, leaving it at 16, which ensures that you can catch it in a TRY ... CATCH block.

    I suppose the question could have included the caveat that it is about returning info from a stored procedure to calling SQL code, rather than to the users in a tool such as Management Studio.

    From any application (via ODBC or DB-LIBRARY) you can get the text returned from PRINT. In dblibrary you should use

    int msg_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity,char *msgtext, char *srvname, char *procname, int line) to intercept any info or

    int err_handler(DBPROCESS * dbproc, int severity, int dberr, int oserr,char *dberrstr, char *oserrstr) to intercept any error.

     

    Here you'll find some example: https://www.freetds.org/userguide/samplecode.htm

  • Lynn Pettis

    SSC Guru

    Points: 442342

    I doubt that using PRINT to return information is used on a regular basis.  The only time I use PRINT for the purpose is for debugging a procedure I am running in SSMS.

    As for RAISERROR and THROW, if you aren't trapping for errors or, if writing to logs checking those, again not a very good way to return info.  Also, I have moved away from PRINT statements for debugging to using RAISERROR with a severity of 10 to return information messages to me in SSMS during debugging sessions for stored procedures.

    Actually returning information to an application, yes there are three means to do that: return values, result sets, and output parameters.

     

  • Rune Bivrin

    SSCertifiable

    Points: 7829

    Carlo Romagnano wrote:

    From any application (via ODBC or DB-LIBRARY) you can get the text returned from PRINT. In dblibrary you should use

    int msg_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity,char *msgtext, char *srvname, char *procname, int line) to intercept any info or

    int err_handler(DBPROCESS * dbproc, int severity, int dberr, int oserr,char *dberrstr, char *oserrstr) to intercept any error.

    Here you'll find some example: https://www.freetds.org/userguide/samplecode.htm

    Yes, I'm well aware of that. Brings back memories of coding C on OS/2 back in '92. I'd still argue it would serve as a "ghetto" approach of returning "info", given that it would preclude using the stored procedure from another procedure with full functionality. As a way to signal errors, or provide progress or debugging info: Absolutely.


    Just because you're right doesn't mean everybody else is wrong.

  • Rune Bivrin

    SSCertifiable

    Points: 7829

    Lynn Pettis wrote:

    I doubt that using PRINT to return information is used on a regular basis.  The only time I use PRINT for the purpose is for debugging a procedure I am running in SSMS.

    As for RAISERROR and THROW, if you aren't trapping for errors or, if writing to logs checking those, again not a very good way to return info.  Also, I have moved away from PRINT statements for debugging to using RAISERROR with a severity of 10 to return information messages to me in SSMS during debugging sessions for stored procedures.

    Actually returning information to an application, yes there are three means to do that: return values, result sets, and output parameters.

    Good summary. And amen to the RAISERROR tip. Used with WITH NOWAIT makes it a very non-intrusive way to track progress.


    Just because you're right doesn't mean everybody else is wrong.

  • dotathinker

    SSC Enthusiast

    Points: 133

    How about temptables, session context, CLR functions, etc.?

  • IMHO

    Ten Centuries

    Points: 1058

    I've got to go with Carlo...the question was about returning info, not just data.  There are clearly more than the three listed answers.  Still, it was a great question because it got me thinking about this subject much more deeply than I have before.

  • Rune Bivrin

    SSCertifiable

    Points: 7829

    dotathinker wrote:

    How about temptables, session context, CLR functions, etc.?

    Yeah, using temp tables is absolutely a workable solution, as is session context. CLR functions probably not, unless they utilize some external storage mechanism to save data across invocations.

    The problem with these is that there isn't an obvious way to know this is happening, and how to interpret the result data. Output parameters, return values and result sets are generally more self evident.


    Just because you're right doesn't mean everybody else is wrong.

  • ScottPletcher

    SSC Guru

    Points: 98484

    I could think of 4 rather easily, I'm sure there's more, as others have noted.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Steve Collins

    SSC Eights!

    Points: 884

    If a stored procedure calls another stored procedure doesn't it return data to the other procedure?  The data returned would be via the input variables of the other procedure(s).

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply