CODEWITHSUNDEEP
sql-serverstored-procedures
Golide
Golide

Reputation: 1009

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 stored procedure in SQL Server, I get all the rows, but when I invoke the same stored procedure from code, it only returns the first row and does not loop the entire resultset.

This is how I'm retrieving the results:

CREATE PROCEDURE [dbo].[GetExpiringCards] 
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;

        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 stored procedure in SSMS I get the following:

QUERY

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?

Upvotes: 0

Views: 1229

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Each FETCH returns one row as a result set back to the application. Although you could change the C# code to handle that (i.e. dr.NextResult()), it would be better to return all the rows in a single query. Below is a single-statement example that also uses a table value constructor instead of table variable.

CREATE PROCEDURE [dbo].[GetExpiringCards] 
AS
SET NOCOUNT ON;
SELECT
      i.value AS Interval
    , DATEADD(DD,value,GETDATE()) AS ExpiresOn
    , CustomerId, IssueBranch 
FROM Cards AS c
CROSS JOIN (SELECT value FROM (VALUES(90),(60),(30),(21),(7),(14)) AS intervals(value)) AS i
WHERE c.ExpiryDate=CONVERT(DATE, DATEADD(DAY, i.value, GETDATE()));

Upvotes: 3

Related Questions