SQL Update Issues While Running Stored Proc

Question

I implement a software application that uses a SQL server database. I'm running a stored proc in a macro I created in my application, however, I cannot get the "StartTime" (datetime field) to update. Here is the code:

ALTER PROCEDURE [dbo].[pAfterFirstRun] ( @scheduleDate datetime ) AS
BEGIN

    SET NOCOUNT ON;

    CREATE TABLE #VehicleId ( VehicleId INT )

    INSERT INTO #VehicleId 
       (VehicleId) 
    SELECT VehicleId 
      FROM ResourceDaily 
     WHERE WorkDay = @scheduleDate 
       AND ResourceName = 'RUN 1' 
       AND ResourceCode IN (SELECT ResourceCode 
                              FROM ResourceDailySchedule 
                             WHERE WorkDay = @scheduleDate)

    UPDATE ResourceDaily 
       SET StartTime = '1899-12-30 07:20:00.000' 
     WHERE WorkDay = @scheduleDate 
       AND ResourceName = 'RUN 2' 
       AND VehicleId IN (SELECT VehicleId 
                           FROM #VehicleId)

    UPDATE ResourceDaily 
       SET StartAddress = '568 BUCKS HILL RD', 
           StartCity = 'WATERBURY', 
           StartState = 'CT', 
           StartZipCode = '06704', 
           StartXCoord = '-73.029938', 
           StartYCoord = '41.606228' 
     WHERE WorkDay = @scheduleDate 
       AND ResourceName = 'RUN 2' 
       AND VehicleId IN (SELECT VehicleId 
                           FROM #VehicleId)

END

I know this is a strange way to accomplish the job; originally, I didn't create a temp table. It was all done through one update statement and I kept changing it to see if I can get it to work.

The original update statement included the updates to StartTime, StartAddress, StartCity, StartState, StartXCoord and StartYCoord all in one statement and everything would update except the StartTime field.

I should also mention that if I were to run the UPDATE statement in Management Studio and set the @scheduleDate to '5/2/2011', the update statement will run correctly... it just doesn't seem to run in the application with the @scheduleDate parameter.

What do you mean by "didn't update"? Did it throw the error, kept the old value or what? — Quassnoi, May 06 '11 at 15:37
Just a side note, don't use nested select statements. SQL is having to execute that nested select statement every time it runs the previous select statement. It's very inefficient. Try using a JOIN or WHERE EXISTS instead. Also, I'm not sure how large your #VehicleId table will get, but you may need to create indexes on it when you're updating or selecting from it — Miles, May 06 '11 at 15:39
The #VehicleId column can only possibly be 8 records max based on the select statement... the stored proc runs inside my application and didn't throw an error in the app.. just said it ran and moved on to the next macro step — Joel, May 06 '11 at 15:51
Also, thank you for the few pointers... these are things that I currently do not know but would like to learn... I just know how to write some of these things but I'm not really conscious of efficiency — Joel, May 06 '11 at 15:55
@Miles: what makes you think that the engine executes the nested statement on every row? — Quassnoi, May 06 '11 at 20:19
@Quassnoi: I thought that was how SQL read and executed the statements... If he had used the nested statement in a FROM statement, it would have only had to join in the records that the statement had returned but since it's in the WHERE clause, I thought it had to execute this for each record in the ResourceDaily table — Miles, May 09 '11 at 13:46
@Miles: have you heard that the engine optimizes the queries? — Quassnoi, May 09 '11 at 15:56
@Quassnoi: Does it really optimize that well? I didn't realize that. I know that in most of the queries I've written in the past couple of years that I've used joins instead of nested select statements just because it was quicker back then. — Miles, May 09 '11 at 20:01
@Miles: optimizes which well? The best way to tell how does the engine optimize the query is to build the execution plan (`Ctrl-L` in `SSMS`) and look at it. The plans may vary but for the @op's query you'll never see anything like "executes the whole nested statement on every row". — Quassnoi, May 09 '11 at 22:12

score 1 · Answer 1 · answered May 06 '11 at 15:41

1

It looks like you're inserting into your table where

ResourceName = 'RUN 1'

and then you're updating it based on where

ResourceName = 'RUN 2'

I'm not sure what your data looks like but that might be an issue

answered May 06 '11 at 15:41

Miles

5,646
18
62
86

Yea this is done intentially... I want to see what vehicles ran "RUN 1" so I can update their starting time for "RUN 2". – Joel May 06 '11 at 15:52

score 0 · Answer 2 · answered May 06 '11 at 15:38

0

Is it a SmallDateTime column? If so, the minumum value is January 1, 1900

answered May 06 '11 at 15:38

stuartd

70,509
14
132
163

score 0 · Accepted Answer · edited May 23 '17 at 12:29

this seems to be data dependent, so run your procedure manually from SQL Server Management Studio, after making these changes to it:

ALTER PROCEDURE [dbo].[pAfterFirstRun] ( @scheduleDate datetime ) AS
BEGIN

    SET NOCOUNT ON;

    CREATE TABLE #VehicleId ( VehicleId INT )

    INSERT INTO #VehicleId 
       (VehicleId) 
    SELECT VehicleId 
      FROM ResourceDaily 
     WHERE WorkDay = @scheduleDate 
       AND ResourceName = 'RUN 1' 
       AND ResourceCode IN (SELECT ResourceCode 
                              FROM ResourceDailySchedule 
                             WHERE WorkDay = @scheduleDate)
--debug
SELECT '#VehicleId' AS "#VehicleId",* FROM #VehicleId 
--debug
SELECT 'UPDATE 1', AS "UPDATE 1", * FROM ResourceDaily 
     WHERE WorkDay = @scheduleDate 
       AND ResourceName = 'RUN 2' 
       AND VehicleId IN (SELECT VehicleId 
                           FROM #VehicleId)

    UPDATE ResourceDaily 
       SET StartTime = '1899-12-30 07:20:00.000' 
     WHERE WorkDay = @scheduleDate 
       AND ResourceName = 'RUN 2' 
       AND VehicleId IN (SELECT VehicleId 
                           FROM #VehicleId)

--debug
SELECT 'UPDATE 2', AS "UPDATE 2", * FROM ResourceDaily 
     WHERE WorkDay = @scheduleDate 
       AND ResourceName = 'RUN 2' 
       AND VehicleId IN (SELECT VehicleId 
                           FROM #VehicleId)


    UPDATE ResourceDaily 
       SET StartAddress = '568 BUCKS HILL RD', 
           StartCity = 'WATERBURY', 
           StartState = 'CT', 
           StartZipCode = '06704', 
           StartXCoord = '-73.029938', 
           StartYCoord = '41.606228' 
     WHERE WorkDay = @scheduleDate 
       AND ResourceName = 'RUN 2' 
       AND VehicleId IN (SELECT VehicleId 
                           FROM #VehicleId)

END

I added 3 debug queries, which will return result sets that you need to look at and understand before you can solve this. I don't know your application or what you are trying to do well enough to fix this for you, but you need to see what your queries are doing before your can solve it. I wouldn't be surprised if it ends up being that #VehicleId is empty after the insert into it. If so, look out for time on the @scheduleDate parameter and time on the WorkDay column values not joining properly. Look into Floor a date in SQL server.

I will try this out and see what happens... I can tell you that table is not empty because if it was empty, the other columns (StartAdress, StartCity, etc.) wouldn't update either which they are currently doing... thank you for the debug code I'm definitely going to try it out!!! — Joel, May 06 '11 at 16:25
So I found out that there is no problem with the SQL code that I had written, the update statement does actually work. Apparently, the issue is with the way the application loads the schedules into memory, so, even though I updated the schedule start time, when I went to load the schedules through the macro step, they were already stored in memory (with the orignal start times). I don't think that I would have found this as quickly as I did without your help KM, thank you very much! — Joel, May 06 '11 at 19:00

SQL Update Issues While Running Stored Proc

3 Answers3