CODEWITHSUNDEEP
t-sql
Nay Lin Aung
Nay Lin Aung

Reputation: 745

Cannot Add Value to Variable in Sql While Loop

In the following while loop, the variable @total is resulted as NULL when adding selected value from table. If not adding, the result is shown as selected value from table but only the last row's value. 

SET @coundDate = '1/1/2012';

WHILE ( Datepart(dd, @countDate) < Datepart(dd, @endDate) )
  BEGIN
      SET @total = @total + (SELECT Cast([7am] AS INT) + 
                                    Cast([8am]AS INT) AS TotalHitCount
                             FROM   Sale
                             WHERE  TransactionDate = @countDate);
      SET @countDate = Dateadd(d, 1, @countDate);
  END;

SELECT @total

I'm now confusing a lot. What's that error?

Upvotes: 0

Views: 1364

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

As Martin points out, you could write your entire query as a simple SUM:

SELECT SUM(Cast([7am] AS INT) + Cast([8am]AS INT)) AS Total
FROM   Sale
WHERE  TransactionDate between '20120101' and @endDate

Although I would query why 7am and 8am aren't already ints.

Upvotes: 1

Sergio
Sergio

Reputation: 6948

You have to pre-init your variable with default value like 0 :)

Upvotes: 1

Related Questions