Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    7

    Unanswered: Stored Procedure not updating table

    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, any ideas why it isn't?. 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

  2. #2
    Join Date
    Feb 2004
    Posts
    88
    The first statement is certainly wrong:

    SELECT @STARTUP_TIME, @SHUTDOWN_TIME FROM TBL_PC_AUDIT

    you can't do that. You have to code:

    SELECT @STARTUP_TIME = <column name>,
    @SHUTDOWN_TIME = <column name>
    FROM TBL_PC_AUDIT
    <where clause ? or is there just one row in the table?>

    There may be other problems, but your first statement will set both @STARTUP_TIME and @SHUTDOWN_TIME to NULL....and nothing'll work from there on in....

  3. #3
    Join Date
    Nov 2009
    Posts
    7
    Thanks for the reply, ya I understand what you are saying thanks for that, Im only starting to work with stored procedures, i changed that and its still not updating my table, i suspect theres a few more errors in the code, ill keep looking thanks

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    ALTER PROCEDURE dbo.UPDATE_TBL_PC_ON (
    	@STARTUP_TIME datetime OUTPUT,
    	@SHUTDOWN_TIME datetime OUTPUT
    )
    AS
    BEGIN --PROC
    	SELECT @STARTUP_TIME = STARTUP_TIME, @SHUTDOWN_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
    I think your code has a few flaws:
    - the SP will update ALL records in TBL_PC_ON (as thompbil also stated), the WHERE clause references no PK.
    - DATEPART(hh, @SHUTDOWN_TIME) <= 23 will never result in FALSE, the domain of hour is 0 .. 23
    - The simultaneous use of >= 7 and <= 7 obfuscates the logic. Though the use of ELSE IF makes that HOURS_ON_DAY and HOURS_ON_NIGHT will never be updated together.

    I think what you really want is that the hours a server is running between 7:00 and 23:00 are added to the HOURS_ON_DAY column and the hours in the other part of the day to the HOURS_ON_NIGHT column.

    Suppose a server (process?) starts at 4:00 and ends the next day at 5:00. How will those hours be calculated? The current SP will update nothing. And if it did, you would loose all the data that was recorded previously.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Apr 2008
    Location
    Along the shores of Lake Michigan
    Posts
    242
    Also, this can never happen, can it??

    IF DATEPART(hh, @STARTUP_TIME) <= 7 AND DATEPART(hh, @SHUTDOWN_TIME) >= 23

    Should be:

    IF DATEPART(hh, @STARTUP_TIME) <= 7 OR DATEPART(hh, @SHUTDOWN_TIME) >= 23

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •