error in sp code.... - Microsoft SQL Server

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

Subscribe Post Reply

1607

Delerna

1,134

Expert 1GB

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

Expand|Select|Wrap|Line Numbers

 
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

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

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_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

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

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

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_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

1,134

Expert 1GB

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

Similar topics

Error during deployment

by: Tony Wright | last post by:

Hi, I am having a problem installing an msi for a web site. The error message I am getting is: "The specified path 'http://mipdev05/features/Fas2' is unavailable. The Internet Information...

.NET Framework

Reference-counted error objects

by: Gregory | last post by:

Hi, One of the disadvantages of using error handling with error codes instead of exception handling is that error codes retuned from a function can be forgotten to check thus leading to...

C / C++

Error Handler best practices

by: deko | last post by:

I use this convention frequently: Exit_Here: Exit Sub HandleErr: Select Case Err.Number Case 3163 Resume Next Case 3376 Resume Next

Microsoft Access / VBA

Error Trapping In Access 2000

by: Peter Frost | last post by:

Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...

Microsoft Access / VBA

Any way to check current On Error status

by: Steve Jorgensen | last post by:

I'm trying to figure out if there is a way to generate standard error handlers that "know" if the calling code has an error handler in effect (On Error Goto <label> or On Error Resume Next) before...

Microsoft Access / VBA

Error on: DoCmd.RunCommand acCmdDeleteRecord

by: Squirrel | last post by:

I have a command button on a subform to delete a record. The only statement in the subroutine is: DoCmd.RunCommand acCmdDeleteRecord The subform's recordsource is "select * from tblVisit order...

Microsoft Access / VBA

Error crystal websetup 9 install

by: p | last post by:

WE had a Crystal 8 WebApp using vs 2002 which we upgraded to VS2003. I also have Crystal 9 pro on my development machine. The web app runs fine on my dev machine but am having problems deploying....

ASP.NET

error handling

by: Eugene Anthony | last post by:

One problem with the code bellow is after this code conn.qDupUser p1,rs I added: set rs = nothing to test the error handling capability.

ASP / Active Server Pages

Custom Error Messages in Access

by: hyperpau | last post by:

Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I...

Visual Basic 4 / 5 / 6

Custom Error Messages

by: hyperpau | last post by:

Microsoft Access / VBA

Wordpress or something else?

by: Faith0G | last post by:

I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

Content Management Systems

Access Europe: Command bars, the Access Shortcut Tool and a simple Audit Log - Wed 3 April

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

General

One-click Importing Excel Data into a*Database

by: ryjfgjl | last post by:

In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...

Microsoft Excel

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware