February 28, 2020 at 12:00 am
Comments posted to this topic are about the item Returning Info from Stored Procedures
February 28, 2020 at 8:01 am
February 28, 2020 at 8:39 am
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.
February 28, 2020 at 9:30 am
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
February 28, 2020 at 9:52 am
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.
February 28, 2020 at 11:33 am
How about outputing data to a table?
February 28, 2020 at 1:10 pm
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
February 28, 2020 at 1:34 pm
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.
February 28, 2020 at 1:36 pm
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.
February 28, 2020 at 1:40 pm
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.
February 28, 2020 at 2:13 pm
How about temptables, session context, CLR functions, etc.?
February 28, 2020 at 3:00 pm
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.
The three biggest mistakes in life...thinking that power = freedom, sex = love, and data = information.
February 28, 2020 at 3:03 pm
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.
February 28, 2020 at 3:28 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 28, 2020 at 4:19 pm
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).
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply