Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010

    Unanswered: Can a Procedure return a table to be used by other script/procedures?

    Here's a question that I can't believe I couldn't find a straight answer to.

    Can a SPROC return a resultset (table) that can be then manipulated by a containing SPROC, or other script?

    If yes, can I see an example somewhere?
    If no, then how else could I accomplish this (extremely common) task:

    I currently have a stored procedure, lets call it GETDATA, that takes some parameters, does some pretty hefty stuff, and returns a big table of summarized and massaged, data.

    The problem is, the calculations are only half done at this point. Depending on what the application is doing, I need more SQL to run on the results of GETDATA.

    So Ideally I will have USEFULPROC1, USEFULPROC2, and USEFULPROC3, which all start by calling GETDATA, but then each continues on, manipulating the results from GETDATA in different ways.

    Of course, I could copy my GETDATA code and paste it 3 times into the USEFULPROCs, but that is an aweful solution.

  2. #2
    Join Date
    Sep 2001
    Chicago, Illinois, USA
    People here who are far sharper than myself can probably give you a specific answer, but if you simply look at the CREATE PROCEDURE help page, it does mention the ability to pass a user-define table type.

    Here is an example from the Help system:

    /* Create a table type. */
    CREATE TYPE LocationTableType AS TABLE
    ( LocationName VARCHAR(50)
    , CostRate INT );

    /* Create a procedure to receive data for the table-valued parameter. */
    CREATE PROCEDURE usp_InsertProductionLocation
    @TVP LocationTableType READONLY
    INSERT INTO [AdventureWorks].[Production].[Location]
    SELECT *, 0, GETDATE()
    FROM @TVP;

    /* Declare a variable that references the type. */
    DECLARE @LocationTVP
    AS LocationTableType;

    /* Add data to the table variable. */
    INSERT INTO @LocationTVP (LocationName, CostRate)
    SELECT [Name], 0.00

    /* Pass the table variable data to a stored procedure. */
    EXEC usp_InsertProductionLocation @LocationTVP;
    Last edited by PracticalProgram; 03-25-10 at 17:18.

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    In 2008 you can pass recordsets in the form of user-defined table datatype as parameters to a stored procedure. Presumably return them as well.
    In 2005 you could perform the same functionality by using a user-defined function instead of a stored procedure, and this may still be the best implementation for what you need.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts