Stored Procedure not updating Fields

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

Stored Procedure not updating Fields

Author

Topic

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-11-24 : 05:25:23

I have a simple stored procedure where it grabs two datetime fields from a table and then gets the datediff between these, it then identifies and splits these hours into two categories, night hours and day hours and updates two fields in a table called TBL_PC_ON. Heres my stored procedure, it executes fine but its not updating any rows. Sorry not very familiar with working with SP's thanks :)

ALTER PROCEDURE dbo.UPDATE_TBL_PC_ON
(
@STARTUP_TIME datetime OUTPUT,
@SHUTDOWN_TIME datetime OUTPUT
)
AS
BEGIN --PROC
SELECT @STARTUP_TIME, @SHUTDOWN_TIME FROM TBL_PC_AUDIT
IF DATEPART(hh, @STARTUP_TIME) >= 7 AND DATEPART(hh,@SHUTDOWN_TIME) <= 23
Begin
UPDATE TBL_PC_ON
SET HOURS_ON_DAY = DATEDIFF(HOUR, @SHUTDOWN_TIME, @STARTUP_TIME)
WHERE STATUS = 'CLOSED';
End --IF
ELSE IF DATEPART(hh, @STARTUP_TIME) <= 7 AND DATEPART(hh, @SHUTDOWN_TIME) >= 23
Begin
UPDATE TBL_PC_ON
SET HOURS_ON_NIGHT = DATEDIFF(HOUR, @SHUTDOWN_TIME, @STARTUP_TIME)
WHERE STATUS = 'CLOSED';
End --IF
END --PROC

niall

prakum
Starting Member

16 Posts

Posted - 2009-11-24 : 06:26:46

Please Provide Sample Data and Table Structure....

Praveen Kumar

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-11-24 : 06:33:19

quote:
Originally posted by prakum

Please Provide Sample Data and Table Structure....

Praveen Kumar

Appreciate the reply, well I just have a table called TBL_PC_AUDIT and 2 fields in that are STARTUP_TIME AND SHUTDOWN_TIME, the data is being pulled from these two fields, I am then using datepart to calculate the difference in the hours between these and subsequently update the TBL_PC_ON, it will update the HOURS_ON_DAY or HOURS_ON_NIGHT depending on whether the hours fall between 7 and 23 or outside this. Thanks

niall

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2009-11-24 : 07:33:01

how are you executing the procedure?

- Jeff
http://weblogs.sqlteam.com/JeffS

nt86
Yak Posting Veteran

54 Posts

Posted - 2009-11-24 : 08:54:47

quote:
Originally posted by jsmith8858

how are you executing the procedure?

- Jeff
http://weblogs.sqlteam.com/JeffS

Jeff thanks for the reply, at the moment im just running it from the Server Explorer. The problem i think is its not getting any values from TBL_PC_ON, i need to get both the STARTUP_TIME and the SHUTDOWN_TIME. Thanks for the reply

niall

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources