Multiple queries in a stored procedure

[Ahamed Azeem]

Hi Gents, I have used two select queries in a stored procedure.. how can i retrieve the values from the two queries in a single datareader for ex, set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER Procedure [dbo].[SearchDoctor] @fname varchar(50), @lname varchar(50) As Select * from docregistration where fname=@fname and lname=@lname Declare @Speciality varchar(50) Select @Speciality=Speciality from docregistration where fname=@fname and lname=@lname Select Speciality from Speciality where SpecialityID=@Speciality GO Thanks a lot in advance Azeem, Senior Dotnet Developer

[The Man from U N C L E]

You can't. A DataReader itterates through a set of rows from a single result table. What you need to use is a DataAdaptor with a dataset. Eg.

System.Data.DataSet myDS = new System.Data.DataSet();
System.Data.SqlClient.SqlDataAdapter myAdaptor
= new System.Data.SqlClient.SqlDataAdapter("Execute myProcedure;"
,new System.Data.SqlClient.SqlConnection(myConnectionString));
myAdaptor.Fill(myDS)

If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk

[WoutL]

Execute DataReader.NextResult() should do the trick.

Wout Louwers

[Ashfield]

The Man from U.N.C.L.E. wrote:

You can't.

Beg to differ. Dataset.Nextresult does it (may have syntax slightly wrong, its not strictly a sql question and I don't have visual studio on this machine)

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP

[The Man from U N C L E]

Good point. I have forgotten that method (on the DataReader actualy), as I tend to use DataReaders for single recordsets and dataadaptor.Fill for multiple record sets.

If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) www.JacksonSoft.co.uk

[Ashfield]

So do I as a rule, but I remembered it from before ADO.NET, when theree were no such things as datasets. Also useful as datareaders are faster than datasets.

Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP