Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Pass RecordSet from store procedure

Posted on 2000-05-15
7
Medium Priority
?
260 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:
Victor Spiridonov earned 400 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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:Victor 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

886 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