Receiving results of a SP result set

Receiving results of a SP result set

Post by David Perkin » Mon, 20 Aug 2001 20:53:59



What's the best way of retrieving a number of parameters from a stored proc

Create Procedure GetMyVals
 AS
    --other processing







    RETURN 0

If the  above was called from another stored proc, how would I retrieve
Name1-6 ?  Is there an alternative to making them all OUTPUT params?

Thanks

David

 
 
 

Receiving results of a SP result set

Post by Dan Guzma » Mon, 20 Aug 2001 22:13:24


As an alternative to output params, the calling procedure USE INSERT ...
EXEC to insert the results into a table.  For example:

CREATE PROCEDURE usp_ExecuteGetMyVals
AS
CREATE TABLE #GetMyVals(
    Name1 varchar(3) NULL,
    Name2 varchar(3) NULL,
    Name3 varchar(3) NULL,
    Name4 varchar(3) NULL,
    Name5 varchar(3) NULL,
    Name6 varchar(3) NULL)
INSERT INTO #GetMyVals EXEC GetMyVals
SELECT * FROM #GetMyVals
DROP TABLE #GetMyVals
RETURN 0
GO

--
Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------


> What's the best way of retrieving a number of parameters from a stored
proc

> Create Procedure GetMyVals
>  AS
>     --other processing







>     RETURN 0

> If the  above was called from another stored proc, how would I retrieve
> Name1-6 ?  Is there an alternative to making them all OUTPUT params?

> Thanks

> David


 
 
 

Receiving results of a SP result set

Post by David Perkin » Mon, 20 Aug 2001 22:23:46


 nm
 
 
 

1. Result Sets vs copying result sets into memory.

Does anyone see a problem with copying a result set into a collection
for display vs looping through the result set for display, so I can
return the connection back to the database little faster? I am using
connection pooling, so there is no connection overhead in creating a
new query.

The only performance issue I can see that each result set will take up
memory, but after it's displayed, that memory will be available for
something else, right?  I'm in a situation where up to 500 people can
hit a webpage for data at the same time.

I'm starting to run out of database connections on the web application
and a bigger database isn't an option for me. I can (and do) cache
persistant non-changing data, but have been told that calls for
changing and updated data must always come straight from the database.

Some of the result sets are over 10000 records long and there's no way
to split them. Through sql, I am able to return only rows X through
X+20, so I only return what I am going to show.

Any POSITIVE feedback would be appreciated.

Thanks in advance,

-- ABS

2. soluction and any elegant way to do it ?

3. Returning a result set from a SP from a SP

4. JOB: US - Irvine,CA -- DBA / Programmer Analyst

5. ADO recordset's problem receive a result recodset from SP

6. Consolidateing Separte regional offices, Suggestions please?

7. How to use a result set from one SP in another SP

8. Run a query

9. Using result set of sp in sp

10. Process sp Result Set Within an sp?

11. Recordset's problem in receiving results from SP

12. Calling a stored procedure from VB 5 and receiving a result set

13. Oracle Gateway- Receiving unexpected result sets after upgrade.