Cannot Add Value to Variable in Sql While Loop

Question

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?

From the code you have shown you wouldn't need a group by. Just a where to select only the rows of interest. — Martin Smith
– Martin Smith, Commented Oct 29, 2012 at 8:25

Damien_The_Unbeliever · Accepted Answer · 2012-10-29 08:30:03Z

1

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.

answered Oct 29, 2012 at 8:30

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Smith Over a year ago

Probably safer to do SUM(ISNULL(Cast([7am] AS INT),0) + ISNULL(Cast([8am] AS INT),0)) AS Total. One of the reasons they might have got their original behaviour would be if one of the values is NULL and this will lose the non null value.

marc_s · Accepted Answer · 2012-10-29 08:24:34Z

1

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

edited Oct 29, 2012 at 8:24

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 29, 2012 at 8:18

Sergio

6,9485 gold badges33 silver badges55 bronze badges

4 Comments

Damien_The_Unbeliever Over a year ago

@NayLinAung - you're not initializing @total in the code you've shown (although the code you've shown obviously isn't your real code)

Nay Lin Aung Over a year ago

@Damien_The_Unbeliever - If you can't help, please don't make useless assumptions. It is the real code in my Business Activity Monitoring System of BIT project. How can I show every pieces of the code? I only posted the part of code where error occurred.

Damien_The_Unbeliever Over a year ago

Useless assumptions? Your first line assigns to a variable called @coundDate, not @countDate. Therefore, this isn't the real code. All we can work with is what you do post.

Nay Lin Aung Over a year ago

@Damien_The_Unbeliever - that's my only typing error. Sorry for that. And I think it is not too much related with the error. If my comment insulted u, I'm really sorry for that. I swear that's my real code and I am here to find out the answer, not to argue about non-related factors.

Collectives™ on Stack Overflow

Cannot Add Value to Variable in Sql While Loop

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related