By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

error in sp code....

P: 53
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_Convertdate_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_GetDaylightSavingsTimeStart(convert(varchar ,datepart(year,getdate())))) = ‘2008-03-09 02:00:00’

(select dbo.fn_GetDaylightSavingsTimeEnd(convert(varchar,d atepart(year,getdate())))) = ‘2008-11-02 02:00:00’

*/

set @DLSStart = (select dbo.fn_GetDaylightSavingsTimeStart(convert(varchar ,datepart(year,getdate()))))

set @DLSEnd = (select dbo.fn_GetDaylightSavingsTimeEnd(convert(varchar,d atepart(year,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
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_Convertdate_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
Oct 19 '08 #1
Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
I am looking at your while loop so I have removed the queries and added comments

Expand|Select|Wrap|Line Numbers
  1. WHILE @@FETCH_STATUS = 0
  2. BEGIN
  3. begin   --Huh...begin what, you have already begun the WHILE loop
  4.  
  5. end    --I suspect the while may be ending here
  6. begin --there should be an if or a while or something to begin
  7.  
  8. end  --Nothing to end
  9.  
  10.  
  11.  
  12. -- The fetch is outside any loop
  13. FETCH NEXT FROM date_cursor into @Date END
  14.  
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
Oct 19 '08 #2

P: 53
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_Convertdate_Deven3 AS
DECLARE @DLSStart smalldatetime
, @DLSEnd smalldatetime
, @Date smalldatetime
SET @DLSStart = (select dbo.fn_GetDaylightSavingsTimeStart(convert(varchar ,datepart(year,getdate()))))
SET @DLSEnd = (select dbo.fn_GetDaylightSavingsTimeEnd(convert(varchar,d atepart(year,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

Expand|Select|Wrap|Line Numbers
  1. WHILE @@FETCH_STATUS = 0
  2. BEGIN
  3. begin   --Huh...begin what, you have already begun the WHILE loop
  4.  
  5. end    --I suspect the while may be ending here
  6. begin --there should be an if or a while or something to begin
  7.  
  8. end  --Nothing to end
  9.  
  10.  
  11.  
  12. -- The fetch is outside any loop
  13. FETCH NEXT FROM date_cursor into @Date END
  14.  
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
Oct 20 '08 #3

P: 53
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_Convertdate_Deven2 AS
declare @DLSStart smalldatetime
, @DLSEnd smalldatetime
, @Date smalldatetime
--, @DLSActive tinyint
set @DLSStart = (select dbo.fn_GetDaylightSavingsTimeStart(convert(varchar ,datepart(year,getdate()))))
set @DLSEnd = (select dbo.fn_GetDaylightSavingsTimeEnd(convert(varchar,d atepart(year,getdate()))))

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

update nextGen_deven
set convertedDate = dateadd(hour, 1, enterDate)
where nextGen_deven.enterdate not between @DLSStart and @DLSEnd
Oct 20 '08 #4

Delerna
Expert 100+
P: 1,134
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 !
Oct 20 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.