Get output of a stored procedure

Question

I have a stored procedure which returns a list of ids of recently updated rows in sql. How can I get as an array or list?

Stored procedure:

CREATE procedure [dbo].[spUpdateResolutionStatus]
@SummaryId int,
@AlertType varchar(500),
@AlertServer varchar(250),
@Subject varchar(250)
as
Begin
declare @insertedIds table (id int)
    Update WCFLogs set ResolutionSummary_OID = @SummaryId, Status_oid = 2
    output inserted.log_oid into @insertedIds
where AlertType=@AlertType and AlertServer= @AlertServer and Log_Subject = @Subject;
select id from @insertedIds
end
GO

My method:

 public int[] UpdateIssueResolutionStatus(Int32 summaryId, string alerttype, string alertserver, string subject)
{
    int[] rowseffected;
    string cs = ConfigurationManager.ConnectionStrings["LogsData"].ConnectionString;
    using (SqlConnection con = new SqlConnection(cs))
    {
        SqlCommand cmd = new SqlCommand("spUpdateResolutionStatus", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@SummaryId", summaryId));
        cmd.Parameters.Add(new SqlParameter("@AlertType", alerttype));
        cmd.Parameters.Add(new SqlParameter("@AlertServer", alertserver));
        cmd.Parameters.Add(new SqlParameter("@Subject", subject));
        con.Open();
        rowseffected =(Int)cmd.ExecuteScalar();
    }
    return rowseffected;
}

ExecuteScalar gives me id of the first row, how can I get the whole list?

You could fill a DataTable with the results, your could use the other `.Execute...` on `SqlCommand`. — Patrick Artner, Nov 10 '17 at 08:27
Too many duplicate ! Question has already been asked. Research solution before posting, Google is your friend — GGO, Nov 10 '17 at 08:30

score 3 · Accepted Answer · answered Nov 10 '17 at 08:28

public int[] UpdateIssueResolutionStatus(Int32 summaryId, string alerttype, string alertserver, string subject)
{
    int[] rowseffected;
    string cs = ConfigurationManager.ConnectionStrings["LogsData"].ConnectionString;
    using (SqlConnection con = new SqlConnection(cs))
    {
        SqlCommand cmd = new SqlCommand("spUpdateResolutionStatus", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@SummaryId", summaryId));
        cmd.Parameters.Add(new SqlParameter("@AlertType", alerttype));
        cmd.Parameters.Add(new SqlParameter("@AlertServer", alertserver));
        cmd.Parameters.Add(new SqlParameter("@Subject", subject));
        con.Open();

        using (IDataReader reader = cmd.ExecuteReader())
        {
            List<int> ids = new List<int>();
            while (reader.Read())
            {
                ids.Add(reader.GetInt32(0));
            }

            return ids.ToArray();
        }
    }
}

Get output of a stored procedure

1 Answers1