Solved

Pass RecordSet from store procedure

Posted on 2000-05-15
7
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 143

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

615 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