Stored procedure returning only first row

I have a stored procedure that checks for Cards that will expire within the next 90, 60, 30, 21, 14 and 7 days. When i execute the SP in Sql Server i am able to get all the rows but when i invoke the same SP from code it only returns first row and does not loop the entire resultset.

This is how im retrieving the results :

CREATE PROCEDURE [dbo].[GetExpiringCards]   -- Add the parameters for the stored procedure here    AS  DECLARE @today datetime2 = cast(GETDATE() as date)  declare @INTERVALS table(intval int)  insert into @INTERVALS (intval) values (90)  insert into @INTERVALS (intval) values (60)  insert into @INTERVALS (intval) values (30)  insert into @INTERVALS (intval) values (21)  insert into @INTERVALS (intval) values (7)  insert into @INTERVALS (intval) values (14)  DECLARE @value int    DECLARE db_cursor CURSOR FOR    SELECT intval FROM @INTERVALS  OPEN db_cursor     FETCH NEXT FROM db_cursor INTO @value       WHILE @@FETCH_STATUS = 0   BEGIN      -- SET NOCOUNT ON added to prevent extra result sets from      -- interfering with SELECT statements.      SET NOCOUNT ON;         SELECT @value AS Interval,DATEADD(DD,@value,@today) AS ExpiresOn, CustomerId, IssueBranch from Cards          where ExpiryDate=CONVERT(DATE, DATEADD(DAY, @value, GETDATE()))         FETCH NEXT FROM db_cursor INTO @value   END  CLOSE db_cursor     DEALLOCATE db_cursor  GO  

In the code i am invoking as :

using (SqlConnection conn = new SqlConnection(DatabaseConnect2))         {             SqlCommand cmd = new SqlCommand("GetExpiringCards", conn);             cmd.CommandType = CommandType.StoredProcedure;             conn.Open();             SqlDataReader dr = cmd.ExecuteReader();             while (dr.Read())             {                 Customer customer = new Customer ();                   customer.Interval = Convert.ToInt32(dr["Interval"]);                 customer.ExpiresOn = Convert.ToDateTime(dr["ExpiresOn"]);                 customer.CustomerId = Convert.ToString(dr["CustomerId"]);                 customer.IssueBranch = Convert.ToString(dr["IssueBranch"]);                 customer.Add(upload);               }             conn.Close();         }  

When i execute the SP in SSMS i get the following :

I have tried changing the logic as below but the flow immediately loops out once it fetches the first row :

using (SqlConnection conn = new SqlConnection(DatabaseConnect2))          {              SqlCommand cmd = new SqlCommand("GetExpiringCards", conn);              cmd.CommandType = CommandType.StoredProcedure;              conn.Open();              SqlDataReader dr = cmd.ExecuteReader();              while (dr.Read() && dr.HasRows)              {                  Customer customer = new Customer ();                    customer.Interval = Convert.ToInt32(dr["Interval"]);                  customer.ExpiresOn = Convert.ToDateTime(dr["ExpiresOn"]);                  customer.CustomerId = Convert.ToString(dr["CustomerId"]);                  customer.IssueBranch = Convert.ToString(dr["IssueBranch"]);                                     customer.Add(upload);                               }              conn.Close();  

What am i missing?

https://stackoverflow.com/questions/65963796/stored-procedure-returning-only-first-row January 30, 2021 at 09:30AM