Reusing Result Set from Stored Procedure

[Hexonx]

Have you ever had a stored procedure that assembles a complex result set for use in a client application, only to later need that same result set for processing in another stored proc?

The problem is how to get hold of that result set.

One way is to simply duplicate the processing code in the calling proc, but this creates a maintenance problem if the processing code needs to be updated in the processing proc (maintenance programmer may be unaware of code duplication).

Another option is to implement an input flag for table output only whereby the processing proc fills a temp table that has to be created in the calling proc. If the processing proc's output changes, the calling proc must be changed to create a compatible temp table (high coupling).

Another method is to provide a cursor output parm in the processing proc. In this case, the output parm is meaningless when the proc is called from the client app. Furthermore, retrofitting a proc with an additional parm may require modifying all calls to the proc everywhere in the system (fragile dependency).

A better way that requires no modification to the processing proc is to store the results of OPENROWSET in a temp table. The following example demonstrates how to do this on MS SQL Server:

SELECT r.* INTO #ProdInfo
FROM OPENROWSET(
  'SQLOLEDB', 
  'MYSERVER'; 
  'sa'; 
  '', 
  'EXEC MyDB.dbo.GetProductInfo 5335')
AS r
-- Display contents of #ProdInfo
SELECT * FROM #ProdInfo

(Note: the comma and semi-colons are not interchangeable)

(Note to experienced DB programmers: Please comment if you have information to contribute on this issue. It will help others in the situation.)

 

[spaliwal]

Another way of using a table variable in a user defined function as given below (sql server 2000)

CREATE FUNCTION MyFuncTab () RETURNS @ProdInfo TABLE
(Column1 <datatype>,
Column2 <datatype>,
.
.
.
ColumnN <datatype>) AS
BEGIN
<Function body>
INSERT @ProdInfo
SELECT * FROM Tab
.
.
.
END






At calling program you have to give

Select * from dbo.MyFuncTab