Stored Procedure with Multiple Select Statements

Question

I have the following stored procedure:

/****** Object:  StoredProcedure [dbo].[OpsToolStats]    Script Date: 09/05/2023 
09:21:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author, , Name>
-- Create Date: <Create Date, , >
-- Description: <Description, , >
-- =============================================
ALTER PROCEDURE [dbo].[OpsToolStats]
(
-- Add the parameters for the stored procedure here
@startDate datetime,
@endDate datetime
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

SELECT count([EndCustomerOrderId]) AS NewOrdersNeedsLabels
FROM [dbo].[EndCustomerOrders]
where ([StatusId] = 0 or [StatusId] = 1000) and CreatedAt >= @startDate and  [CreatedAt] 
<= @endDate

SELECT count([EndCustomerOrderId]) AS APIErrorCeatingLabels
FROM [dbo].[EndCustomerOrders]
where [StatusId] = 1100 and CreatedAt >= @startDate and  [CreatedAt] <= @endDate

SELECT count([EndCustomerOrderId]) AS ScheduleCollection
FROM [dbo].[EndCustomerOrders]
where ([StatusId] = 1300 or [StatusId] = 1500 or [StatusId] = 1700) and CreatedAt >= 
@startDate and  [CreatedAt] <= @endDate

END

In SQL Enterprise manager the results visually look like this:

I am trying to work out how to call this SP and get the results of the individual select statements:

I have the following code:

var command = _dbLive.Database.GetDbConnection().CreateCommand();

        command.CommandText = "OpsToolStats";
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@startDate", "2023-05-01"));
        command.Parameters.Add(new SqlParameter("@endDate", "2023-05-31"));

        _dbLive.Database.OpenConnection();

        using var result = command.ExecuteReader();
        var dataTable = new DataTable();
        dataTable.Load(result);

But I only get 1 column and 1 row in the datatable... for the "NewOrdersNeedsLabels"..

Can anyone suggest how to get all the values please?

You are returning multiple result sets but you are only consuming the *first* result set in your C# code. Does this answer your question? [How do I return multiple result sets with SqlCommand?](https://stackoverflow.com/questions/12715620/how-do-i-return-multiple-result-sets-with-sqlcommand) — Thom A, May 09 '23 at 09:41
Though, in truth, considering all your queries are against `dbo.EndCustomerOrders` I'm not sure why you don't return a single data set with many columns. It'll likely be far more performant. — Thom A, May 09 '23 at 09:44
Looks like you could combine them into one query `SELECT count(case when StatusId IN (0, 1000) then EndCustomerOrderId end), count(case when StatusId IN (1100) then EndCustomerOrderId end)...` — Charlieface, May 09 '23 at 10:23

Dai · Accepted Answer · 2023-05-09T10:33:55.250

You're using DataTable.Load which only loads the first resultset - it will ignore subsequent resultsets.
Instead, use a DataSet object (which itself contains multiple DataTable objects: one for each resultsset): as DataSet.Load will attempt to consume all resultsets from a DbDataReader for you.
If you're using SqlDataReader directly (instead of letting DataTable.Load, DataSet.Load or SqlDataAdapter do all the hard work for you) then you will need to call NextResult() after you finish your while( rdr.Read() ) loop for the first result-set - and then have separate while( rdr.Read() ) loops for all expected subsequent results.
- It is important that you always check if NextResult() returns true or false and put the effort in to gracefully handle unexpecedly fewer actualk resultsets (this can happen if your PROCEDURE or statement-batch errors out.

So this, basically:

Also:
- Always specify exact SqlDbType values for every SqlParameter because ADO.NET's type-inference is unhelpfully simplistic.
- Where-possible (i.e. on .NET 7+ with the latest Microsoft.Data.SqlClient library) when using SQL Server's date for parameters you should use .NET's DateOnly value type instead of DateTime - and especially not date-string-literals - your current code as-is right now could potentially misbehave if thread culture/localization settings align with the stars.

SqlConnection c = _dbLive.Database.GetDbConnection();

SqlCommand command = c.CreateCommand();

command.CommandText = "OpsToolStats";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@startDate", SqlDbType.Date)).Value = new DateOnly( 2023, 5, 1 ) );
command.Parameters.Add(new SqlParameter("@endDate", SqlDbType.Date)).Value = new DateOnly( 2023, 5, 31 ) );

if( c.State != ConnectionState.Open ) c.OpenConnection();

using( SqlDataReader rdr = command.ExecuteReader() ) )
{
    DataSet ds = new DataSet();
    ds.Load( rdr );
}

score 0 · Answer 2 · answered May 09 '23 at 09:54

many thanks for the pointers.. this is what i ended up with:

using (SqlConnection conn = new SqlConnection(_config["ConnectionStringLive"]))
        {
            var dataset = new DataSet();
            var adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand("OpsToolStats", conn)
            {
                CommandType = CommandType.StoredProcedure
            };
            adapter.SelectCommand.Parameters.Add(new SqlParameter("@startDate", "2023-05-01"));
            adapter.SelectCommand.Parameters.Add(new SqlParameter("@endDate", "2023-05-31"));
            adapter.Fill(dataset);

            Sherpr.Model.Model.Calendar.BucketStats stats = new Sherpr.Model.Model.Calendar.BucketStats
            {
                NewOrdersNeedsLabels = (int)dataset.Tables[0].Rows[0].Table.Rows[0].ItemArray[0]
            };

        }

You don't need to use `SqlDataAdapter` at all here - it's unnecessary as `DataSet` and `DataTable` are capable of populating themselves from a `DbDataReader` without the need for a `DataAdapter` (`DataAdapter` exists to help with two-way updatable data operations - which your code doesn't seem to be performing, therefore there is no reason to use it here). — Dai, May 09 '23 at 09:57

Stored Procedure with Multiple Select Statements

2 Answers2