Multiple separate sql queries in one stored procedure

Question

I need many (16) different queries to get the number of rows at a time. I had 16 different connections, but I thought that I could only combine one stored procedure.

I am having problems, as this gives an error for the datareader (system out of range) for this line:

Count2 = Convert.ToInt32(objDRL("Row_Count2").ToString). 

Here is my code:

 Dim strConn As String = "Data Source=myDataSource" Dim Conn As New SqlConnection(strConn) Dim Cmd As New SqlCommand("adminStats", Conn) Cmd.CommandType = CommandType.StoredProcedure Dim objDRL As SqlDataReader Cmd.Parameters.Add(New SqlParameter("@campDate", "June 2014")) Conn.Open() objDRL = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection) If objDRL.HasRows Then While objDRL.Read() Count1 = Convert.ToInt32(objDRL("Row_Count1")) Count2 = Convert.ToInt32(objDRL("Row_Count2")) Count3 = Convert.ToInt32(objDRL("Row_Count3")) Count4 = Convert.ToInt32(objDRL("Row_Count4")) Count5 = Convert.ToInt32(objDRL("Row_Count5")) Count6 = Convert.ToInt32(objDRL("Row_Count6")) Count7 = Convert.ToInt32(objDRL("Row_Count7")) Count8 = Convert.ToInt32(objDRL("Row_Count8")) Count9 = Convert.ToInt32(objDRL("Row_Count9")) Count10 = Convert.ToInt32(objDRL("Row_Count10")) Count11 = Convert.ToInt32(objDRL("Row_Count11")) Count12 = Convert.ToInt32(objDRL("Row_Count12")) Count13 = Convert.ToInt32(objDRL("Row_Count13")) Count14 = Convert.ToInt32(objDRL("Row_Count14")) Count15 = Convert.ToInt32(objDRL("Row_Count15")) End While Else End If link1.Text = Count1.ToString link2.Text = Count2.ToString link3.Text = Count3.ToString link4.Text = Count4.ToString Link5.Text = Count5.ToString Link6.Text = Count6.ToString Link7.Text = Count7.ToString Link8.Text = Count8.ToString Link9.Text = Count9.ToString Link10.Text = Count10.ToString Link11.Text = Count11.ToString Link12.Text = Count12.ToString Link13.Text = Count13.ToString Link14.Text = Count14.ToString Link15.Text = Count15.ToString Conn.Close() 

Stored Procedure:

 CREATE PROCEDURE adminStats @campDate VARCHAR(20) AS BEGIN SELECT COUNT(*) AS Row_Count1 FROM Customer SELECT COUNT(*) AS Row_Count2 FROM campRegistration WHERE campDate = @campDate SELECT COUNT(*) AS Row_Count3 FROM campRegistration WHERE paidFull = 'True' SELECT COUNT(*) AS Row_Count4 FROM campRegistration WHERE shirtSize = 'S' SELECT COUNT(*) AS Row_Count5 FROM campRegistration WHERE shirtSize = 'M' SELECT COUNT(*) AS Row_Count6 FROM campRegistration WHERE shirtSize = 'L' SELECT COUNT(*) AS Row_Count7 FROM campRegistration WHERE shirtSize = 'XL' SELECT COUNT(*) AS Row_Count8 FROM campRegistration WHERE shirtSize = 'XXL' SELECT COUNT(*) AS Row_Count9 FROM campRegistration WHERE Staff = 'True' SELECT COUNT(*) AS Row_Count10 FROM campRegistration WHERE days = '1' SELECT COUNT(*) AS Row_Count11 FROM campRegistration WHERE days = '2' SELECT COUNT(*) AS Row_Count12 FROM campRegistration WHERE days = '3' SELECT COUNT(*) AS Row_Count13 FROM campRegistration WHERE days = '12' SELECT COUNT(*) AS Row_Count14 FROM campRegistration WHERE days = '23' SELECT COUNT(*) AS Row_Count15 FROM campRegistration WHERE days = '123' END GO 

Answer 1

Try changing your request on this -

 CREATE PROCEDURE adminStats @campDate varChar(20) AS BEGIN SELECT (SELECT COUNT(1) FROM dbo.Customer) AS Row_Count1 , COUNT(CASE WHEN campDate = @campDate THEN 1 END) AS Row_Count2 , COUNT(CASE WHEN paidFull = 'True' THEN 1 END) AS Row_Count3 , COUNT(CASE WHEN shirtSize = 'S' THEN 1 END) AS Row_Count4 , COUNT(CASE WHEN shirtSize = 'M' THEN 1 END) AS Row_Count5 , COUNT(CASE WHEN shirtSize = 'L' THEN 1 END) AS Row_Count6 , COUNT(CASE WHEN shirtSize = 'XL' THEN 1 END) AS Row_Count7 , COUNT(CASE WHEN shirtSize = 'XXL' THEN 1 END) AS Row_Count8 , COUNT(CASE WHEN Staff = 'True' THEN 1 END) AS Row_Count9 , COUNT(CASE WHEN [days] = '1' THEN 1 END) AS Row_Count10 , COUNT(CASE WHEN [days] = '2' THEN 1 END) AS Row_Count11 , COUNT(CASE WHEN [days] = '3' THEN 1 END) AS Row_Count12 , COUNT(CASE WHEN [days] = '12' THEN 1 END) AS Row_Count13 , COUNT(CASE WHEN [days] = '23' THEN 1 END) AS Row_Count14 , COUNT(CASE WHEN [days] = '123' THEN 1 END) AS Row_Count15 FROM dbo.campRegistration WHERE [days] IN ('1', '2', '3', '12', '23', '123') OR shirtSize IN ('S', 'M', 'L', 'XL', 'XXL') OR Staff = 'True' OR paidFull = 'True' OR campDate = @campDate END 

For more information on rechecking, read this topic .

Answer 2

---------------------------------- C # code ----------- - -------------------------------

FOR INSERT

  cmd = new SqlCommand("sp_UserLogin", con); cmd.CommandType = CommandType.StoredProcedure; try { cmd.Parameters.AddWithValue("@op", "Insert"); cmd.Parameters.AddWithValue("@UserName", userdetail.UserName); cmd.Parameters.AddWithValue("@Password", userdetail.Password); cmd.Parameters.AddWithValue("@ConfirPassword", userdetail.ConfirmPassword); cmd.ExecuteNonQuery(); } 

FOR CHOOSE

  cmd = new SqlCommand("sp_UserLogin", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataReader dr; try { cmd.Parameters.AddWithValue("@op", "Select"); dr = cmd.ExecuteReader(); } 

-------------------------------- Saving procedure ------------- --- -------------------

 Create PROCEDURE [dbo].[sp_UserLogin] -- Add the parameters for the stored procedure here @op varchar(50)='g', @UserName varchar(50)=null, @Password varchar(100)=null, @ConfirPassword varchar(100)=null AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. if @op='Insert' BEGIN -- Insert statements for procedure here insert into UserLogin values (@UserName,@Password,@ConfirPassword) End if @op='Update' BEGIN -- Insert statements for procedure here Select * from UserLogin End END