SQL Server Stored Procedure with Multiple Select Statements

hc8w905p 于 2023-05-12 发布在其他

关注(0)|答案(2)|浏览(240)

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?

sql-server

来源：https://stackoverflow.com/questions/76207894/stored-procedure-with-multiple-select-statements

2条答案

按热度按时间

i1icjdpr1#

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.Loadwill 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 );
}

赞(0）回复(0）举报 2023-05-12

chy5wohz2#

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]
            };

        }

赞(0）回复(0）举报 2023-05-12

我来回答

SQL Server Stored Procedure with Multiple Select Statements

2条答案

相关问题

热门标签

最新问答