Solved

Pass RecordSet from store procedure

Posted on 2000-05-15
7
229 Views
Last Modified: 2012-08-13
i have a select a "Select" statement in store procedure which get many records from different table and fetch into the cursor.
How can i pass out the recordset.
I use ODBC to connect to the SQL server 7
0
Comment
Question by:wpkhoo
7 Comments
 
LVL 6

Expert Comment

by:Jon_Raymond
ID: 2812996
Does the stored procedure use a cursor or is it a client generated cursor?  If it's a stored procedure you could insert the data into a temp table.  Then query that table or export it from SQL Server.  If it's a client cursor you need a destination grid or table or something to hold the data.  What client are you using Access?, VB?
0
 
LVL 7

Accepted Solution

by:
spiridonov earned 200 total points
ID: 2813190
You can save the records to temp table and just do a SELECT from this table at the end of the procedure. Then on your client you will have to call
OpenRecordset(EXECUTE your_sp)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2813214
I think that you might not need the cursor.
You procedure should look like this:

CREATE PROC YourProc
AS
SELECT * FROM YourTable

And, as previous comments indicated, simply issue a "EXEC YourProc" instead of the usual "SELECT * FROM Table" will return you the recordset.



0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:wpkhoo
ID: 2813331
Reply to Jon/spiridonov
It is a cursor, and the client is MFC.
How could i create the temp table (please give in details). Need to delate the temp table ?
On my client i used "exec my_sp " this statement to call the Stored Procedure.
does i cause any problem if i used the temp table and several client module run the Stored Procedure at the same time.

Reply to angelIII
I need the cursor because i need the stored all the selected records, then do some process on it and only pass out some of the records which meet the requirement.

I appreciate for all yours help.
thanks
0
 

Author Comment

by:wpkhoo
ID: 2813420
Reply to Jon/spiridonov
It is a cursor, and the client is MFC.
How could i create the temp table (please give in details). Need to delate the temp table ?
On my client i used "exec my_sp " this statement to call the Stored Procedure.
does i cause any problem if i used the temp table and several client module run the Stored Procedure at the same time.

Reply to angelIII
I need the cursor because i need the stored all the selected records, then do some process on it and only pass out some of the records which meet the requirement.

I appreciate for all yours help.
thanks
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 2813574
You can create temp table as usuall table using CREATE TABLE statement, you just need to put # in front of the table name (CREATE TABLE #aaa...

You don't need to delete it, it will be deleted automatically after sp execution.

There will be no problem with multiple clients, separate  temp table will be created for each instance of sp ( for each connection).
0
 

Author Comment

by:wpkhoo
ID: 2813707
yup....Thanks I got the answer
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question