Rune Bivrin wrote: Carlo Romagnano wrote: Rune Bivrin wrote: Carlo Romagnano wrote:
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