Wednesday, June 15, 2011

How to share data between stored procedures ? Good Link

Discusses in detail various methods :

This table summarises the methods:

MethodInput/ OutputSQL Server versionsComment
Using OUTPUT ParametersOutputAllNot generally applicable, but sometimes overlooked.
Table-valued FunctionsOutputSQL 2000Probably the best method for output, but has some restrictions.
Inline FunctionsUse this when you want to reuse a single SELECT.
Multi-statement FunctionsWhen you need to encapsulate more complex logic.
Using a TableIn/OutAllMost general methods with no restrictions, but a little more complex to use.
Sharing a Temp TableMainly for single pair of caller/callee.
Process-keyed TableBest choice for many callers to same callee.
Global Temp TablesA variation of Process-Keyed.
INSERT-EXECOutputSQL 6.5Does not require rewrite. Has some gotchas.
Table Parameters and Table TypesIn/(Out)SQL 2008Could have been the final answer, but due to a restriction it is only mildly useful in this context.
Using the CLROutputSQL 2005Does not require a rewrite. Clunky, but is useful as a last resort when INSERT-EXEC does not work.
OPENQUERYOutputSQL 7Does not require rewrite. Tricky with many pitfalls.
Using XMLIn/OutSQL 2005A roundabout way that requires you to make a rewrite, but it has some advantages over the other methods.
Using Cursor VariablesOutputSQL 7Not recommendable.

Have a look at other articles also on the home page :

  • The curse and blessings of dynamic SQL. How you use dynamic SQL, when you should - and when you should not. (Added June 2001, Completely revised: April 2006. Most recent update: December 2008.) German translations of an old version available.

  • Arrays and Lists in SQL Server. Several methods on how to pass an array of values from a client to SQL Server, and performance data about the methods. There are three articles: one for SQL 2008 which is devoted entirely to table-valued parameters, one for SQL 2005 and later (for situations when you cannot use TVPs), and one for SQL 2000 and earlier. (Added March 2003. Version for SQL 2005 added March 2007, major revision and addition of article for SQL 2008: January 2010. Most recent update October 2010.)

  • Dynamic Search Conditions. How to write a stored procedure that permits users to select among many search conditions, using both dynamic and static SQL. There are two articles: one for SQL 2008, and one for SQL 2005 and earlier. (Added May 2003. Most recent update: November 2009. French and German translations of the SQL 2005 version available.)

  • How to share data between stored procedures. Different ways of passing sets of data between stored procedures. (Added July 2001, Most recent update: January 2010)

  • Giving Permissions through Stored Procedures. SQL 2005 adds two new methods – signing with certificates and impersonation with EXECUTE AS – that can manage cases where the classic method of ownership chaining fails. I explain the two new methods, as well as the old one, and warn you about the pitfalls. (Added February 2006)

  • Slow in the Application, Fast in SSMS? Ever experienced the situation that your query runs slow in your application, but when you try it in SQL Server Management Studio, the response is immediate? This article explains why this may happen, what parameter sniffing is and suggests methods to analyse and address your performance problem. (Added February 2011.)

  • Build Your Own Index. This is a teaser for.. Well, follow the link and read on! (Added November 2009.)

  • Error Handling in SQL 2005 and Later. This is an unfinished article, which consists only of a jumpstart section that shows how to use BEGIN-TRY. The full article will appear ...sometime. The article also gives you links to my old articles for error handling in SQL 2000. (Added July 2003. Most recent update: November 2009 )

  • Connecting to MS SQL Server from Unix. A brief summary of what's available. Since I don't work with Unix myself, it's not extremely well-informed, but people have kept asking me about this. (Added Jan 1999, Latest update: June 2008)

  • No comments:

    Post a Comment