Multiple queries in a stored procedure
-
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
-
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
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
-
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
-
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
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. 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
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
-
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