03-25-10, 17:30 #1Registered User
- 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.
03-25-10, 18:11 #2Registered User
- 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
SET NOCOUNT ON
INSERT INTO [AdventureWorks].[Production].[Location]
SELECT *, 0, GETDATE()
/* Declare a variable that references the type. */
/* 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 18:18.Ken
Maverick Software Design
(847) 864-3600 x2
03-25-10, 21:15 #3World Class Flame Warrior
Provided Answers: 1
- Join Date
- Jun 2003
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.