trying to implement simple while loop

14 Jan 2006 2:44 PM

hazz

why am I getting the TSQL error "Incorrect syntax near the keyword 'begin'."
can you see what I am trying to do? I want to print out counts for a date
based query as I decrement the date with each loop.
thank you . -greg

USE db
GO
declare @now datetime
declare @currentcount int
set @now = '2006-01-14'

while 1
begin
set @currentcount = (select count(Create_DT) from customer where var_DT

> @now );

@now = @now - 1
print @currentcount
end

14 Jan 2006 2:51 PM

Andrew J. Kelly

Couple of things. One is there is no way to exit the loop so it will be
continuous. Second is you should use DATEADD() to decrement the date instead
of -1 as I don't believe it will do what you expect. And last try the while
as shown below.

while 1 = 1
begin
set @currentcount = (select count(Create_DT) from customer where var_DT

> @now );

     @now = DATEADD(dd,-1,@now)
     print @currentcount

    IF SomeConditon = true
            BREAK
end

--
Andrew J. Kelly SQL MVP

Show quote

"hazz" <h***@sonic.net> wrote in message
news:Orq$wiRGGHA.2696@TK2MSFTNGP14.phx.gbl...
> why am I getting the TSQL error "Incorrect syntax near the keyword
> 'begin'."
> can you see what I am trying to do? I want to print out counts for a date
> based query as I decrement the date with each loop.
> thank you . -greg
>
> USE db
> GO
> declare @now datetime
> declare @currentcount int
> set @now = '2006-01-14'
>
> while 1
> begin
>     set @currentcount = (select count(Create_DT) from customer where
> var_DT
> > @now );
>     @now = @now - 1
>     print @currentcount
> end
>

14 Jan 2006 3:10 PM

hazz

Thank you Andrew. I just added the set keyword in front of @now =
DATEADD(dd,-1,@now) and it works ! -greg

Show quote

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:uq4eEoRGGHA.2704@TK2MSFTNGP15.phx.gbl...
> Couple of things. One is there is no way to exit the loop so it will be
> continuous. Second is you should use DATEADD() to decrement the date
> instead of -1 as I don't believe it will do what you expect. And last try
> the while as shown below.
>
>
> while 1 = 1
> begin
>     set @currentcount = (select count(Create_DT) from customer where
> var_DT
> > @now );
>
>     @now = DATEADD(dd,-1,@now)
>     print @currentcount
>
>    IF SomeConditon = true
>            BREAK
> end
>
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "hazz" <h***@sonic.net> wrote in message
> news:Orq$wiRGGHA.2696@TK2MSFTNGP14.phx.gbl...
>> why am I getting the TSQL error "Incorrect syntax near the keyword
>> 'begin'."
>> can you see what I am trying to do? I want to print out counts for a date
>> based query as I decrement the date with each loop.
>> thank you . -greg
>>
>> USE db
>> GO
>> declare @now datetime
>> declare @currentcount int
>> set @now = '2006-01-14'
>>
>> while 1
>> begin
>>     set @currentcount = (select count(Create_DT) from customer where
>> var_DT
>> > @now );
>>     @now = @now - 1
>>     print @currentcount
>> end
>>
>
>