error in sp code....

[Deven Oza]

hi,
Please help me in following code of stored procedure….

My table and data: select * from nextGen_deven
--------------------------------------------------------------------------
Name1 enterdate convertedDate
Deven 2008-01-05 00:00:00.000 NULL
Deven1 2008-02-05 10:10:10.000 NULL
Deven2 2008-03-05 10:20:10.000 NULL
Deven3 2008-04-05 22:22:00.000 NULL
Deven4 2008-05-05 22:22:00.000 NULL
Deven5 2008-06-05 22:22:00.000 NULL
Deven6 2008-07-05 22:22:00.000 NULL
Deven7 2008-08-05 22:22:00.000 NULL
Deven8 2008-09-05 22:22:00.000 NULL
Deven9 2008-10-05 22:22:00.000 NULL
Deven10 2008-11-05 22:22:00.000 NULL
Deven11 2008-12-05 22:22:00.000 NULL

I have written stored procedure to add 2 hours or 1 hour in table data.

create PROCEDURE aim_sp_Convertd ate_Deven AS
declare @DLSStart smalldatetime
, @DLSEnd smalldatetime
, @Date smalldatetime

/* please note: following two functions return me ‘2008-03-09 02:00:00’ and ‘2008-11-02 02:00:00’ values

(select dbo.fn_GetDayli ghtSavingsTimeS tart(convert(va rchar ,datepart(year, getdate())))) = ‘2008-03-09 02:00:00’

(select dbo.fn_GetDayli ghtSavingsTimeE nd(convert(varc har,d atepart(year,ge tdate())))) = ‘2008-11-02 02:00:00’

*/

set @DLSStart = (select dbo.fn_GetDayli ghtSavingsTimeS tart(convert(va rchar ,datepart(year, getdate()))))

set @DLSEnd = (select dbo.fn_GetDayli ghtSavingsTimeE nd(convert(varc har,d atepart(year,ge tdate()))))


DECLARE date_cursor CURSOR FOR
(select enterdate from nextGen_deven)
OPEN date_cursor
FETCH NEXT FROM date_cursor into @Date
WHILE @@FETCH_STATUS = 0
BEGIN
begin
update NextGen_Deven
set convertedDate = dateadd(hour, 1, enterDate)
where @Date between @DLSStart and @DLSEnd
end
begin
update NextGen_Deven
set convertedDate = dateadd(hour, 2, enterDate)
where @Date not between @DLSStart and @DLSEnd
end
FETCH NEXT FROM date_cursor into @Date END
CLOSE date_cursor
DEALLOCATE date_cursor



When I execute this procedure, it updates every row by adding 2 hours (not 1 hour in some of row)

Begin Tran abc
exec aim_sp_Convertd ate_Deven
select * from nextGen_deven

my updated table data
--------------------------------------------------------------------------
Name1 enterdate convertedDate
Deven 2008-01-05 00:00:00.000 2008-01-05 02:00:00.000
Deven1 2008-02-05 10:10:10.000 2008-02-05 12:10:10.000
Deven2 2008-03-05 10:20:10.000 2008-03-05 12:20:10.000
Deven3 2008-04-05 22:22:00.000 2008-04-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven4 2008-05-05 22:22:00.000 2008-05-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven5 2008-06-05 22:22:00.000 2008-06-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven6 2008-07-05 22:22:00.000 2008-07-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven7 2008-08-05 22:22:00.000 2008-08-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven8 2008-09-05 22:22:00.000 2008-09-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven9 2008-10-05 22:22:00.000 2008-10-06 00:22:00.000 (should add 1 hour only, not 2 hours)
Deven10 2008-11-05 22:22:00.000 2008-11-06 00:22:00.000
Deven11 2008-12-05 22:22:00.000 2008-12-06 00:22:00.000


Thanks,
Deven Oza

[Delerna]

I am looking at your while loop so I have removed the queries and added comments

[code=sql"]
WHILE @@FETCH_STATUS = 0
BEGIN
begin --Huh...begin what, you have already begun the WHILE loop

end --I suspect the while may be ending here
begin --there should be an if or a while or something to begin

end --Nothing to end



-- The fetch is outside any loop
FETCH NEXT FROM date_cursor into @Date END
[/code]

Did this code actually run like this or have you edited it after pasting into your post?

I am also looking at the set for @DLSEnd and notice the eronious space d atepart

That should raise an error so I ask did you edit the code after pasting it and we are not seeing the full story? The while structure just dosn't look right to me.

Regards

[Deven Oza]

Thank you for looking at my code.... that's true that I made some changes after posting it here.. Here I am posting my updated code... the problem is that the only ELSE part is working no matter what date I pass...
-------------------------------------------------------------------------
CREATE PROCEDURE aim_sp_Convertd ate_Deven3 AS
DECLARE @DLSStart smalldatetime
, @DLSEnd smalldatetime
, @Date smalldatetime
SET @DLSStart = (select dbo.fn_GetDayli ghtSavingsTimeS tart(convert(va rchar,datepart( year,getdate()) )))
SET @DLSEnd = (select dbo.fn_GetDayli ghtSavingsTimeE nd(convert(varc har,datepart(ye ar,getdate()))) )


DECLARE date_cursor CURSOR FOR
(select enterdate from nextGen_deven)
OPEN date_cursor
FETCH NEXT FROM date_cursor into @Date
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM date_cursor into @Date

if @Date between @DLSStart and @DLSEnd
Begin
update NextGen_Deven
set convertedDate = dateadd(hour, 2, enterDate)
END
Else
Begin
update NextGen_Deven
set convertedDate = dateadd(hour, 1, enterDate)
END
END
CLOSE date_cursor
DEALLOCATE date_cursor
-------------------------------------------------------------------------

I am looking at your while loop so I have removed the queries and added comments

[code=sql"]
WHILE @@FETCH_STATUS = 0
BEGIN
begin --Huh...begin what, you have already begun the WHILE loop

end --I suspect the while may be ending here
begin --there should be an if or a while or something to begin

end --Nothing to end



-- The fetch is outside any loop
FETCH NEXT FROM date_cursor into @Date END
[/code]

Did this code actually run like this or have you edited it after pasting into your post?

I am also looking at the set for @DLSEnd and notice the eronious space d atepart

That should raise an error so I ask did you edit the code after pasting it and we are not seeing the full story? The while structure just dosn't look right to me.

Regards

[Deven Oza]

Hey my code is working now by doing simply deleting cursor, now I am using simple two update statements and it is working fine, anyway thank you very much you looked at it.

ALTER PROCEDURE aim_sp_Convertd ate_Deven2 AS
declare @DLSStart smalldatetime
, @DLSEnd smalldatetime
, @Date smalldatetime
--, @DLSActive tinyint
set @DLSStart = (select dbo.fn_GetDayli ghtSavingsTimeS tart(convert(va rchar,datepart( year,getdate()) )))
set @DLSEnd = (select dbo.fn_GetDayli ghtSavingsTimeE nd(convert(varc har,datepart(ye ar,getdate()))) )

update nextGen_deven
set convertedDate = dateadd(hour, 2, enterDate)
where nextGen_deven.e nterdate between @DLSStart and @DLSEnd

update nextGen_deven
set convertedDate = dateadd(hour, 1, enterDate)
where nextGen_deven.e nterdate not between @DLSStart and @DLSEnd

[Delerna]

Yep, that was going to be my next question.
Steer clear of cursors if at all possible.

Good work getting it working. There's nothing like solving a problem !