| Author |
 Topic  |
|
|
dshachar
Starting Member
3 Posts |
 Posted - 06/06/2012 : 08:09:33
|
What is wrong with this code?
use [Stored_procedures_DB]
Declare @Month table(MYDay date)
declare @v_date date , @mydate date
Declare @BOM Date = '20120129'
declare @i int
set @i = 0
set @v_date=@BOM
while @v_date < DATEADD(day,-1,GETDATE())
Begin
set @v_date =(DATEADD(DAY,@i,@BOM))
print 'MONTH DATE '+CONVERT(VARCHAR, @v_date, 103)+' '+CONVERT(VARCHAR, DATEADD(day,1,GETDATE()), 103)
insert into @Month(MYDay) Values (@v_date)
SET @i += 1
End
declare c_date cursor for
select MYDay from @Month
Open c_date
fetch next from c_date into @mydate
while @@FETCH_STATUS = 0 --@mydate < DATEADD(day,1,GETDATE())--(@@FETCH_STATUS <> -1)
begin
print 'DATE '+CONVERT(VARCHAR, @mydate, 103)
end
close c_date
DEALLOCATE c_date
I'm getting an infinit loop with only the first date.
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 06/06/2012 : 08:35:04
|
I think this accomplishes the same thing as yours, but more efficiently.
Declare @Month table(MYDay date)
declare @v_date date , @mydate date
Declare @BOM Date = '20120129'
declare @i int
SELECT dateadd(day,spt.number,@BOM)
FROM master..spt_values spt
WHERE spt.type = 'p' and
dateadd(day,spt.number,@BOM) < DATEADD(day,-1,GETDATE())
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
dshachar
Starting Member
3 Posts |
Posted - 06/06/2012 : 08:41:21
|
Thank you jimf'
but I need to run a cursor and I'm getting the same.
Declare @Month table(MYDay date)
declare @v_date date , @mydate date
Declare @BOM Date = '20120129'
declare @i int
declare c_date cursor for
SELECT dateadd(day,spt.number,@BOM) dd
FROM master..spt_values spt
WHERE spt.type = 'p' and
dateadd(day,spt.number,@BOM) < DATEADD(day,-1,GETDATE())
open c_date
fetch next from c_date into @mydate
while @@FETCH_STATUS = 0
begin
print 'DATE '+CONVERT(VARCHAR, @mydate, 103)
end
close c_date
DEALLOCATE c_date |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 06/06/2012 : 09:29:44
|
quote:
but I need to run a cursor
Why?SELECT 'DATE ' + CONVERT(char(10),dateadd(day,spt.number,@BOM),103)
FROM master..spt_values spt
WHERE spt.type = 'p' and
dateadd(day,spt.number,@BOM) < DATEADD(day,-1,GETDATE()) That gives you exactly the same output, you don't need a cursor at all. |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 06/06/2012 : 09:33:02
|
You need a fetch inside the loop to move to the next row (just before the end?).
Don't you want an order by in the cursor?
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
vijays3
Constraint Violating Yak Guru
India
311 Posts |
Posted - 06/06/2012 : 16:42:43
|
quote:
Originally posted by dshachar
What is wrong with this code?
use [Stored_procedures_DB]
Declare @Month table(MYDay date)
declare @v_date date , @mydate date
Declare @BOM Date = '20120129'
declare @i int
set @i = 0
set @v_date=@BOM
while @v_date < DATEADD(day,-1,GETDATE())
Begin
set @v_date =(DATEADD(DAY,@i,@BOM))
print 'MONTH DATE '+CONVERT(VARCHAR, @v_date, 103)+' '+CONVERT(VARCHAR, DATEADD(day,1,GETDATE()), 103)
insert into @Month(MYDay) Values (@v_date)
SET @i += 1
End
declare c_date cursor for
select MYDay from @Month
Open c_date
fetch next from c_date into @mydate
while @@FETCH_STATUS = 0 --@mydate < DATEADD(day,1,GETDATE())--(@@FETCH_STATUS <> -1)
begin
print 'DATE '+CONVERT(VARCHAR, @mydate, 103)
fetch next from c_date into @mydate
end
close c_date
DEALLOCATE c_date
I'm getting an infinit loop with only the first date.
add fetch next from c_date into @mydate in your cursor ..I hae marked that in BOLD
Vijay is here to learn something from you guys. |
|
|
|
dshachar
Starting Member
3 Posts |
Posted - 06/07/2012 : 01:55:29
|
thank you for all  |
|
|
|
vijays3
Constraint Violating Yak Guru
India
311 Posts |
Posted - 06/07/2012 : 09:49:48
|
Most welcome
Vijay is here to learn something from you guys. |
|
|
| |
Topic |
|
|
|
Please help in cursor
