Solved

Pass RecordSet from store procedure

Posted on 2000-05-15
7
217 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

912 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now