Please help in cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help in cursor

Author

Topic

dshachar
Starting Member

3 Posts

Posted - 2012-06-06 : 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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-06 : 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 - 2012-06-06 : 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

15732 Posts

Posted - 2012-06-06 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-06 : 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

354 Posts

Posted - 2012-06-06 : 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 - 2012-06-07 : 01:55:29

thank you for all

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-06-07 : 09:49:48

Most welcome

Vijay is here to learn something from you guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources