Multiple Queries in a Stored Procedure

[mrfritz44]

I'm trying to consolidate a series of 14 SQL queries in an ASP page into a single stored procedure. How do I write the S/P so that I can access all of the queried fields as a single recordset? As I have it now, I am only able to access data from the first query that is executed within the S/P. It's as if the rest of the data doesn't exist when run from the ASP page. If I run the S/P in query analyzer though, I can see all 14 queries have been executed properly.

Any hints?

Thanks,

Fritz

 

[swampBoogie]

It's an ado issue.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthnextrec.asp

 

[SQLSister]

The answer is you probably can't get them all into a single recordset unless they have the same structure. In this case you can union them together. You probably don't want to do this if the queries are very different.

In this case you have two choices. First, ASP must have a mtehod to move from recordset to recordset. I know you can do this in ASP.Net because we do it in our websites all the time. Not sure about regular ASP, it would be best to ask in the ASP forum.

Alternative is to create and run 14 stored procedures from your web page. But the moving from recordset to recordset thing is really the better choice.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[miq]

HI,
have you used nextrecordset or something like this. I have used asp and remember something like this had been achieved in the past.

B.R,
miq

 

[mrfritz44]

I reaserched the nextrecordset funciton but I'm unsure on how to implement it within my application with ADO. Any code snippets?

Thank you......

Fred

 

[JamesLean]

You will probably need to post this question in a forum relating to your specific application language as this is not really a SQL Server question any more.

--James