Solved

Pass RecordSet from store procedure

Posted on 2000-05-15
7
208 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

759 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

23 Experts available now in Live!

Get 1:1 Help Now