all bits considered data to information to knowledge

21Jan/100

SQL Server: passing data between procedures

The common programming task - passing parameters between functions - is far from simple in Transact-SQL. One has to pay close attention to a particular version of the RDBMS that implements the language. To add to confusion, ever since Microsoft SQL Server and Sybase had parted their ways (version 7.0 and 11.5, respectively), there are two ever diverging dialects of Transact-SQL.

This article How to Share Data Between Stored Procedures  by  Erland Sommarskog goes into excruciating details explaining different options a programmer has when there is a need to pass data between stored procedure. Saved my team some time, and provided an opportunity to learn. Thank you!

The following table is taken verbatim from the original post by Mr. Sommarskog, and links back to his site:

Method Input/ Output SQL Server versions Comment
Using OUTPUT Parameters Output All Not generally applicable, but sometimes overlooked.
Table-valued Functions Output SQL 2000 Probably the best method for output, but has some restrictions.
Inline Functions Use this when you want to reuse a single SELECT.
Multi-statement Functions When you need to encapsulate more complex logic.
Using a Table In/Out All Most general methods with no restrictions, but a little more complex to use.
Sharing a Temp Table Mainly for single pair of caller/callee.
Process-keyed Table Best choice for many callers to same callee.
Global Temp Tables A variation of Process-Keyed.
INSERT-EXEC Output SQL 6.5 Does not require rewrite. Has some gotchas.
Table Parameters and Table Types In/(Out) SQL 2008 Could have been the final answer, but due to a restriction it is only mildly useful in this context.
Using the CLR Output SQL 2005 Does not require a rewrite. Clunky, but is useful as a last resort when INSERT-EXEC does not work.
OPENQUERY Output SQL 7 Does not require rewrite. Tricky with many pitfalls.
Using XML In/Out SQL 2005 A roundabout way that requires you to make a rewrite, but it has some advantages over the other methods.
Using Cursor Variables Output SQL 7 Not recommendable.