Faster SQL Code


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Faster SQL Code

  1. #1
    Edwin Guest

    Faster SQL Code


    Hello all,

    I've written a stored procedure that runs in a DTS package. I have to compare
    records in two tables one by one so I use a cursor. It also calls two other
    SPs but these are quite simple. One adds records, the other deletes them.

    It takes about 13 minutes to run but I was wondering if there is anything
    in my SQL code that slows it down. If you have time, can you please glance
    at the code (sorry, it's quite long!) and point out if there are some things
    that can be done quicker.

    CREATE PROCEDURE usp_Update_WipStat

    --Will check to see if the record exists in the WipStatNew table.
    --If the record does not exists in WipStatNew, it will populate the WipStatNew
    --table with all the records in Wip_Temp * the number of boards.
    --If it does exist in WipStatNew it will check to see if the data matches
    --what the data are in Wip_Temp (the most recent data) and make the updates
    --if they are necessary and delete as many records as are there are less
    records
    --in WipStatNew minus Wip_Temp

    AS

    DECLARE @job_number char(10) --We need to job number in order to create
    the "123-01,123-02" naming scheme
    DECLARE @wipstat_boards int --Used to determine the less recent number of
    boards per job
    DECLARE @extra_boards int --Used to tell the delete stored procedure how
    many records it must delete
    DECLARE @customer_id char(10)
    DECLARE @account_manager char(3)
    DECLARE @original_due_date varchar(50)
    DECLARE @job_description char(15)
    DECLARE @job_type char(15)
    DECLARE @job_size char (50)
    DECLARE @prep char(50)
    DECLARE @finish char(50)
    DECLARE @pack char(50)
    DECLARE @job_date varchar(50)
    DECLARE @project_id varchar(50)
    DECLARE @number_of_boards nvarchar(50)

    --Declare and open a cursor in order to scroll through every record
    DECLARE Wip_Boards CURSOR

    FOR SELECT job_number,
    cuid,
    account_manager,
    original_due_date,
    job_description,
    job_type,
    job_size,
    prep,
    finish,
    pack,
    job_date,
    project_id,
    number_of_boards

    FROM Wip_Temp --Need to add fields being matched

    FOR READ ONLY

    OPEN Wip_Boards

    --While there is a next record...
    WHILE @@FETCH_STATUS = 0 --If it equals zero, it succefully browsed to the
    next record

    BEGIN

    --...get the Job Number and the number of boards from the Cursor and put
    them in variables
    FETCH NEXT

    FROM Wip_Boards
    --Need to add cuid
    INTO @job_number,
    @customer_id,
    @account_manager,
    @original_due_date,
    @job_description,
    @job_type,
    @job_size,
    @prep,
    @finish,
    @pack,
    @job_date,
    @project_id,
    @number_of_boards

    --Use the variables that you got from the Cursor to run the stored procedure
    as many times as needed.

    --Need to check this first...add if the records are not new and a loop
    if they are new.

    --Only do this for records that are not already in WipStatNew
    IF @job_number NOT IN (SELECT jobnumber FROM Web_WipStatNew)
    BEGIN
    EXECUTE usp_Update_Wip_Boards @job_number,@number_of_boards
    END

    --If they already are in WipStatNew, then check to see if the data is
    the same
    IF @job_number IN (SELECT jobnumber FROM Web_WipStatNew)

    --If the record is already in WipStat, you need to check if the other
    data that is coming in from Wip_Temp match WipStatNew
    BEGIN

    IF @account_manager NOT IN (SELECT SalesRep FROM Web_WipStatNew WHERE
    JobNumber = @job_number)
    BEGIN
    UPDATE Web_WipStatNew
    SET SalesRep = @account_manager
    WHERE JobNumber = @job_number
    END

    IF @original_due_date NOT IN (SELECT OriginalDueDate FROM Web_WipStatNew
    WHERE JobNumber = @job_number)
    BEGIN
    UPDATE Web_WipStatNew
    SET OriginalDueDate = @original_due_date
    WHERE JobNumber = @job_number
    END

    IF @job_description NOT IN (SELECT JobDescription FROM Web_WipStatNew
    WHERE JobNumber = @job_number)
    BEGIN
    UPDATE Web_WipStatNew
    SET JobDescription = @job_description
    WHERE JobNumber = @job_number
    END

    IF @job_type NOT IN (SELECT JobType FROM Web_WipStatNew WHERE JobNumber
    = @job_number)
    BEGIN
    UPDATE Web_WipStatNew
    SET JobType = @Job_Type
    WHERE JobNumber = @job_number
    END

    IF @job_size NOT IN (SELECT JobSize FROM Web_WipStatNew WHERE JobNumber
    = @job_number)
    BEGIN
    UPDATE Web_WipStatNew
    SET JobSize = @job_size
    WHERE JobNumber = @job_number
    END

    IF @prep NOT IN (SELECT Prep FROM Web_WipStatNew WHERE JobNumber =
    @job_number)
    BEGIN
    UPDATE Web_WipStatNew
    SET Prep = @prep
    WHERE JobNumber = @job_number
    END

    IF @finish NOT IN (SELECT Finish FROM Web_WipStatNew WHERE JobNumber
    = @job_number)
    BEGIN
    UPDATE Web_WipStatNew
    SET Finish = @finish
    WHERE JobNumber = @job_number
    END

    IF @pack NOT IN (SELECT Pack FROM Web_WipStatNew WHERE JobNumber =
    @job_number)
    BEGIN
    UPDATE Web_WipStatNew
    SET Pack = @pack
    WHERE JobNumber = @job_number
    END

    IF @job_date NOT IN (SELECT JobDate FROM Web_WipStatNew WHERE JobNumber
    = @job_number)
    BEGIN
    UPDATE Web_WipStatNew
    SET JobDate = @job_date
    WHERE JobNumber = @job_number
    END

    IF @project_id NOT IN (SELECT ProjectID FROM Web_WipStatNew WHERE JobNumber
    = @job_number)
    BEGIN
    UPDATE Web_WipStatNew
    SET ProjectID = @project_id
    WHERE JobNumber = @job_number
    END

    ---Check for number of boards per job
    --If the number of boards in WipStatNew does not match the one in Wip_Temp...

    IF @number_of_boards NOT IN (SELECT count(Jobnumber) FROM Web_WipStatNew
    WHERE jobnumber = @job_number)
    BEGIN
    --find out how many are in WipStat..
    SELECT @wipstat_boards = (SELECT count(Jobnumber) FROM Web_WipStatNew
    WHERE jobnumber = @job_number)
    END
    --and if there are less boards in Wip_Temp than in WipStatNew...
    IF @number_of_boards < @wipstat_boards
    BEGIN
    --find out the difference...
    SELECT @extra_boards = @wipstat_boards - @number_of_boards
    --and delete as many records as there are more in WipStatNew than
    Wip_Temp
    EXECUTE usp_Delete_Boards @job_number, @extra_boards

    END

    --but if there are less boards in WipStatNew than in Wip_Temp...
    IF @number_of_boards > @wipstat_boards
    BEGIN
    --find out the difference...
    SELECT @extra_boards = @number_of_boards - @wipstat_boards
    --and add as many records as there are more in Wip_Temp than in
    WipStatNew
    EXECUTE usp_Update_Wip_Boards @job_number, @extra_boards
    END
    END

    --If there is a next record to fetch, continue the loop. If not, stop
    the loop.
    IF @@FETCH_STATUS = 0
    CONTINUE
    ELSE
    BREAK



    END

    --Close the Cursor and free the system resources by deallocating it.
    CLOSE Wip_Boards
    DEALLOCATE Wip_Boards



    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO




  2. #2
    Kevin Guest

    Re: Faster SQL Code


    Some odds and ends. I don't know how much of this may help.

    There is a FAST_FORWARD switch for cursors. It is suppose to speed up single
    forward only passes through the data sets.

    Have you ran SQL profiler or looked at the execution plan in query analyzer
    to find out if any particular query stands out as being slower than others?

    It appears there could be multiple records with the same job number? If so,
    I would think a clustered index on job number in WipStatNew would be called
    for, if there isn't one.

    You should be able to do an Else on the if @Job_Number not in...,
    although this would only eliminate one query.

    Can your multiple select ... not in / update section work like the following?
    All the statements you have could be replaced with this one update.

    update Web_WipStatNew
    set SalesRep = case when SalesRep <> @account_manager then @account_manager
    else SalesRep end,
    set OriginalDueDate = case when OriginalDueDate <> @original_due_date then
    @original_due_date else OriginalDueDate end,
    set JobDescription = case when JobDescription <> @job_description then @job_description
    else JobDescription end,
    etc.
    where JobNumber = @job_number

    I would think this would be faster.

    Good luck,
    Kevin


    "Edwin" <vbedluciano@hotmail.com> wrote:
    >
    >Hello all,
    >
    >I've written a stored procedure that runs in a DTS package. I have to compare
    >records in two tables one by one so I use a cursor. It also calls two other
    >SPs but these are quite simple. One adds records, the other deletes them.
    >
    >It takes about 13 minutes to run but I was wondering if there is anything
    >in my SQL code that slows it down. If you have time, can you please glance
    >at the code (sorry, it's quite long!) and point out if there are some things
    >that can be done quicker.
    >
    >CREATE PROCEDURE usp_Update_WipStat
    >
    >--Will check to see if the record exists in the WipStatNew table.
    >--If the record does not exists in WipStatNew, it will populate the WipStatNew
    >--table with all the records in Wip_Temp * the number of boards.
    >--If it does exist in WipStatNew it will check to see if the data matches
    >--what the data are in Wip_Temp (the most recent data) and make the updates
    >--if they are necessary and delete as many records as are there are less
    >records
    >--in WipStatNew minus Wip_Temp
    >
    > AS
    >
    > DECLARE @job_number char(10) --We need to job number in order to create
    >the "123-01,123-02" naming scheme
    > DECLARE @wipstat_boards int --Used to determine the less recent number

    of
    >boards per job
    > DECLARE @extra_boards int --Used to tell the delete stored procedure how
    >many records it must delete
    > DECLARE @customer_id char(10)
    > DECLARE @account_manager char(3)
    > DECLARE @original_due_date varchar(50)
    > DECLARE @job_description char(15)
    > DECLARE @job_type char(15)
    > DECLARE @job_size char (50)
    > DECLARE @prep char(50)
    > DECLARE @finish char(50)
    > DECLARE @pack char(50)
    > DECLARE @job_date varchar(50)
    > DECLARE @project_id varchar(50)
    > DECLARE @number_of_boards nvarchar(50)
    >
    > --Declare and open a cursor in order to scroll through every record
    > DECLARE Wip_Boards CURSOR
    >
    > FOR SELECT job_number,
    > cuid,
    > account_manager,
    > original_due_date,
    > job_description,
    > job_type,
    > job_size,
    > prep,
    > finish,
    > pack,
    > job_date,
    > project_id,
    > number_of_boards
    >
    > FROM Wip_Temp --Need to add fields being matched
    >
    > FOR READ ONLY
    >
    > OPEN Wip_Boards
    >
    > --While there is a next record...
    > WHILE @@FETCH_STATUS = 0 --If it equals zero, it succefully browsed to

    the
    >next record
    >
    > BEGIN
    >
    > --...get the Job Number and the number of boards from the Cursor and put
    >them in variables
    > FETCH NEXT
    >
    > FROM Wip_Boards
    > --Need to add cuid
    > INTO @job_number,
    > @customer_id,
    > @account_manager,
    > @original_due_date,
    > @job_description,
    > @job_type,
    > @job_size,
    > @prep,
    > @finish,
    > @pack,
    > @job_date,
    > @project_id,
    > @number_of_boards
    >
    > --Use the variables that you got from the Cursor to run the stored procedure
    >as many times as needed.
    >
    > --Need to check this first...add if the records are not new and a loop
    >if they are new.
    >
    > --Only do this for records that are not already in WipStatNew
    > IF @job_number NOT IN (SELECT jobnumber FROM Web_WipStatNew)
    > BEGIN
    > EXECUTE usp_Update_Wip_Boards @job_number,@number_of_boards
    > END
    >
    > --If they already are in WipStatNew, then check to see if the data is
    >the same
    > IF @job_number IN (SELECT jobnumber FROM Web_WipStatNew)
    >
    > --If the record is already in WipStat, you need to check if the other
    >data that is coming in from Wip_Temp match WipStatNew
    > BEGIN
    >
    > IF @account_manager NOT IN (SELECT SalesRep FROM Web_WipStatNew WHERE
    >JobNumber = @job_number)
    > BEGIN
    > UPDATE Web_WipStatNew
    > SET SalesRep = @account_manager
    > WHERE JobNumber = @job_number
    > END
    >
    > IF @original_due_date NOT IN (SELECT OriginalDueDate FROM Web_WipStatNew
    > WHERE JobNumber = @job_number)
    > BEGIN
    > UPDATE Web_WipStatNew
    > SET OriginalDueDate = @original_due_date
    > WHERE JobNumber = @job_number
    > END
    >
    > IF @job_description NOT IN (SELECT JobDescription FROM Web_WipStatNew
    > WHERE JobNumber = @job_number)
    > BEGIN
    > UPDATE Web_WipStatNew
    > SET JobDescription = @job_description
    > WHERE JobNumber = @job_number
    > END
    >
    > IF @job_type NOT IN (SELECT JobType FROM Web_WipStatNew WHERE JobNumber
    >= @job_number)
    > BEGIN
    > UPDATE Web_WipStatNew
    > SET JobType = @Job_Type
    > WHERE JobNumber = @job_number
    > END
    >
    > IF @job_size NOT IN (SELECT JobSize FROM Web_WipStatNew WHERE JobNumber
    >= @job_number)
    > BEGIN
    > UPDATE Web_WipStatNew
    > SET JobSize = @job_size
    > WHERE JobNumber = @job_number
    > END
    >
    > IF @prep NOT IN (SELECT Prep FROM Web_WipStatNew WHERE JobNumber =
    >@job_number)
    > BEGIN
    > UPDATE Web_WipStatNew
    > SET Prep = @prep
    > WHERE JobNumber = @job_number
    > END
    >
    > IF @finish NOT IN (SELECT Finish FROM Web_WipStatNew WHERE JobNumber
    >= @job_number)
    > BEGIN
    > UPDATE Web_WipStatNew
    > SET Finish = @finish
    > WHERE JobNumber = @job_number
    > END
    >
    > IF @pack NOT IN (SELECT Pack FROM Web_WipStatNew WHERE JobNumber =
    >@job_number)
    > BEGIN
    > UPDATE Web_WipStatNew
    > SET Pack = @pack
    > WHERE JobNumber = @job_number
    > END
    >
    > IF @job_date NOT IN (SELECT JobDate FROM Web_WipStatNew WHERE JobNumber
    >= @job_number)
    > BEGIN
    > UPDATE Web_WipStatNew
    > SET JobDate = @job_date
    > WHERE JobNumber = @job_number
    > END
    >
    > IF @project_id NOT IN (SELECT ProjectID FROM Web_WipStatNew WHERE

    JobNumber
    >= @job_number)
    > BEGIN
    > UPDATE Web_WipStatNew
    > SET ProjectID = @project_id
    > WHERE JobNumber = @job_number
    > END
    >
    > ---Check for number of boards per job
    > --If the number of boards in WipStatNew does not match the one in Wip_Temp...
    >
    > IF @number_of_boards NOT IN (SELECT count(Jobnumber) FROM Web_WipStatNew
    > WHERE jobnumber = @job_number)
    > BEGIN
    > --find out how many are in WipStat..
    > SELECT @wipstat_boards = (SELECT count(Jobnumber) FROM Web_WipStatNew
    > WHERE jobnumber = @job_number)
    > END
    > --and if there are less boards in Wip_Temp than in WipStatNew...
    > IF @number_of_boards < @wipstat_boards
    > BEGIN
    > --find out the difference...
    > SELECT @extra_boards = @wipstat_boards - @number_of_boards
    > --and delete as many records as there are more in WipStatNew than
    >Wip_Temp
    > EXECUTE usp_Delete_Boards @job_number, @extra_boards


    >
    > END
    >
    > --but if there are less boards in WipStatNew than in Wip_Temp...
    > IF @number_of_boards > @wipstat_boards
    > BEGIN
    > --find out the difference...
    > SELECT @extra_boards = @number_of_boards - @wipstat_boards
    > --and add as many records as there are more in Wip_Temp than in
    >WipStatNew
    > EXECUTE usp_Update_Wip_Boards @job_number, @extra_boards
    > END
    > END
    >
    > --If there is a next record to fetch, continue the loop. If not, stop
    >the loop.
    > IF @@FETCH_STATUS = 0
    > CONTINUE
    > ELSE
    > BREAK
    >
    >
    >
    > END
    >
    > --Close the Cursor and free the system resources by deallocating it.
    > CLOSE Wip_Boards
    > DEALLOCATE Wip_Boards
    >
    >
    >
    >GO
    >SET QUOTED_IDENTIFIER OFF
    >GO
    >SET ANSI_NULLS ON
    >GO
    >
    >
    >



  3. #3
    Edwin Guest

    Re: Faster SQL Code


    Thanks so much. I had no idea the case statements were faster.

    "Kevin" <kevjv@hotmail.com> wrote:
    >
    >Some odds and ends. I don't know how much of this may help.
    >
    >There is a FAST_FORWARD switch for cursors. It is suppose to speed up single
    >forward only passes through the data sets.
    >
    >Have you ran SQL profiler or looked at the execution plan in query analyzer
    >to find out if any particular query stands out as being slower than others?
    >
    >It appears there could be multiple records with the same job number? If

    so,
    >I would think a clustered index on job number in WipStatNew would be called
    >for, if there isn't one.
    >
    >You should be able to do an Else on the if @Job_Number not in...,
    >although this would only eliminate one query.
    >
    >Can your multiple select ... not in / update section work like the following?
    >All the statements you have could be replaced with this one update.
    >
    >update Web_WipStatNew
    > set SalesRep = case when SalesRep <> @account_manager then @account_manager
    >else SalesRep end,
    > set OriginalDueDate = case when OriginalDueDate <> @original_due_date then
    >@original_due_date else OriginalDueDate end,
    > set JobDescription = case when JobDescription <> @job_description then

    @job_description
    >else JobDescription end,
    > etc.
    >where JobNumber = @job_number
    >
    >I would think this would be faster.
    >
    >Good luck,
    >Kevin
    >
    >
    >"Edwin" <vbedluciano@hotmail.com> wrote:
    >>
    >>Hello all,
    >>
    >>I've written a stored procedure that runs in a DTS package. I have to compare
    >>records in two tables one by one so I use a cursor. It also calls two other
    >>SPs but these are quite simple. One adds records, the other deletes them.
    >>
    >>It takes about 13 minutes to run but I was wondering if there is anything
    >>in my SQL code that slows it down. If you have time, can you please glance
    >>at the code (sorry, it's quite long!) and point out if there are some things
    >>that can be done quicker.
    >>
    >>CREATE PROCEDURE usp_Update_WipStat
    >>
    >>--Will check to see if the record exists in the WipStatNew table.
    >>--If the record does not exists in WipStatNew, it will populate the WipStatNew
    >>--table with all the records in Wip_Temp * the number of boards.
    >>--If it does exist in WipStatNew it will check to see if the data matches
    >>--what the data are in Wip_Temp (the most recent data) and make the updates
    >>--if they are necessary and delete as many records as are there are less
    >>records
    >>--in WipStatNew minus Wip_Temp
    >>
    >> AS
    >>
    >> DECLARE @job_number char(10) --We need to job number in order to create
    >>the "123-01,123-02" naming scheme
    >> DECLARE @wipstat_boards int --Used to determine the less recent number

    >of
    >>boards per job
    >> DECLARE @extra_boards int --Used to tell the delete stored procedure how
    >>many records it must delete
    >> DECLARE @customer_id char(10)
    >> DECLARE @account_manager char(3)
    >> DECLARE @original_due_date varchar(50)
    >> DECLARE @job_description char(15)
    >> DECLARE @job_type char(15)
    >> DECLARE @job_size char (50)
    >> DECLARE @prep char(50)
    >> DECLARE @finish char(50)
    >> DECLARE @pack char(50)
    >> DECLARE @job_date varchar(50)
    >> DECLARE @project_id varchar(50)
    >> DECLARE @number_of_boards nvarchar(50)
    >>
    >> --Declare and open a cursor in order to scroll through every record
    >> DECLARE Wip_Boards CURSOR
    >>
    >> FOR SELECT job_number,
    >> cuid,
    >> account_manager,
    >> original_due_date,
    >> job_description,
    >> job_type,
    >> job_size,
    >> prep,
    >> finish,
    >> pack,
    >> job_date,
    >> project_id,
    >> number_of_boards
    >>
    >> FROM Wip_Temp --Need to add fields being matched
    >>
    >> FOR READ ONLY
    >>
    >> OPEN Wip_Boards
    >>
    >> --While there is a next record...
    >> WHILE @@FETCH_STATUS = 0 --If it equals zero, it succefully browsed to

    >the
    >>next record
    >>
    >> BEGIN
    >>
    >> --...get the Job Number and the number of boards from the Cursor and

    put
    >>them in variables
    >> FETCH NEXT
    >>
    >> FROM Wip_Boards
    >> --Need to add cuid
    >> INTO @job_number,
    >> @customer_id,
    >> @account_manager,
    >> @original_due_date,
    >> @job_description,
    >> @job_type,
    >> @job_size,
    >> @prep,
    >> @finish,
    >> @pack,
    >> @job_date,
    >> @project_id,
    >> @number_of_boards
    >>
    >> --Use the variables that you got from the Cursor to run the stored procedure
    >>as many times as needed.
    >>
    >> --Need to check this first...add if the records are not new and a loop
    >>if they are new.
    >>
    >> --Only do this for records that are not already in WipStatNew
    >> IF @job_number NOT IN (SELECT jobnumber FROM Web_WipStatNew)
    >> BEGIN
    >> EXECUTE usp_Update_Wip_Boards @job_number,@number_of_boards
    >> END
    >>
    >> --If they already are in WipStatNew, then check to see if the data is
    >>the same
    >> IF @job_number IN (SELECT jobnumber FROM Web_WipStatNew)
    >>
    >> --If the record is already in WipStat, you need to check if the other
    >>data that is coming in from Wip_Temp match WipStatNew
    >> BEGIN
    >>
    >> IF @account_manager NOT IN (SELECT SalesRep FROM Web_WipStatNew WHERE
    >>JobNumber = @job_number)
    >> BEGIN
    >> UPDATE Web_WipStatNew
    >> SET SalesRep = @account_manager
    >> WHERE JobNumber = @job_number
    >> END
    >>
    >> IF @original_due_date NOT IN (SELECT OriginalDueDate FROM Web_WipStatNew
    >> WHERE JobNumber = @job_number)
    >> BEGIN
    >> UPDATE Web_WipStatNew
    >> SET OriginalDueDate = @original_due_date
    >> WHERE JobNumber = @job_number
    >> END
    >>
    >> IF @job_description NOT IN (SELECT JobDescription FROM Web_WipStatNew
    >> WHERE JobNumber = @job_number)
    >> BEGIN
    >> UPDATE Web_WipStatNew
    >> SET JobDescription = @job_description
    >> WHERE JobNumber = @job_number
    >> END
    >>
    >> IF @job_type NOT IN (SELECT JobType FROM Web_WipStatNew WHERE JobNumber
    >>= @job_number)
    >> BEGIN
    >> UPDATE Web_WipStatNew
    >> SET JobType = @Job_Type
    >> WHERE JobNumber = @job_number
    >> END
    >>
    >> IF @job_size NOT IN (SELECT JobSize FROM Web_WipStatNew WHERE JobNumber
    >>= @job_number)
    >> BEGIN
    >> UPDATE Web_WipStatNew
    >> SET JobSize = @job_size
    >> WHERE JobNumber = @job_number
    >> END
    >>
    >> IF @prep NOT IN (SELECT Prep FROM Web_WipStatNew WHERE JobNumber

    =
    >>@job_number)
    >> BEGIN
    >> UPDATE Web_WipStatNew
    >> SET Prep = @prep
    >> WHERE JobNumber = @job_number
    >> END
    >>
    >> IF @finish NOT IN (SELECT Finish FROM Web_WipStatNew WHERE JobNumber
    >>= @job_number)
    >> BEGIN
    >> UPDATE Web_WipStatNew
    >> SET Finish = @finish
    >> WHERE JobNumber = @job_number
    >> END
    >>
    >> IF @pack NOT IN (SELECT Pack FROM Web_WipStatNew WHERE JobNumber

    =
    >>@job_number)
    >> BEGIN
    >> UPDATE Web_WipStatNew
    >> SET Pack = @pack
    >> WHERE JobNumber = @job_number
    >> END
    >>
    >> IF @job_date NOT IN (SELECT JobDate FROM Web_WipStatNew WHERE JobNumber
    >>= @job_number)
    >> BEGIN
    >> UPDATE Web_WipStatNew
    >> SET JobDate = @job_date
    >> WHERE JobNumber = @job_number
    >> END
    >>
    >> IF @project_id NOT IN (SELECT ProjectID FROM Web_WipStatNew WHERE

    >JobNumber
    >>= @job_number)
    >> BEGIN
    >> UPDATE Web_WipStatNew
    >> SET ProjectID = @project_id
    >> WHERE JobNumber = @job_number
    >> END
    >>
    >> ---Check for number of boards per job
    >> --If the number of boards in WipStatNew does not match the one in

    Wip_Temp...
    >>
    >> IF @number_of_boards NOT IN (SELECT count(Jobnumber) FROM Web_WipStatNew
    >> WHERE jobnumber = @job_number)
    >> BEGIN
    >> --find out how many are in WipStat..
    >> SELECT @wipstat_boards = (SELECT count(Jobnumber) FROM Web_WipStatNew
    >> WHERE jobnumber = @job_number)
    >> END
    >> --and if there are less boards in Wip_Temp than in WipStatNew...
    >> IF @number_of_boards < @wipstat_boards
    >> BEGIN
    >> --find out the difference...
    >> SELECT @extra_boards = @wipstat_boards - @number_of_boards
    >> --and delete as many records as there are more in WipStatNew than
    >>Wip_Temp
    >> EXECUTE usp_Delete_Boards @job_number, @extra_boards


    >
    >>
    >> END
    >>
    >> --but if there are less boards in WipStatNew than in Wip_Temp...
    >> IF @number_of_boards > @wipstat_boards
    >> BEGIN
    >> --find out the difference...
    >> SELECT @extra_boards = @number_of_boards - @wipstat_boards
    >> --and add as many records as there are more in Wip_Temp than in
    >>WipStatNew
    >> EXECUTE usp_Update_Wip_Boards @job_number, @extra_boards
    >> END
    >> END
    >>
    >> --If there is a next record to fetch, continue the loop. If not, stop
    >>the loop.
    >> IF @@FETCH_STATUS = 0
    >> CONTINUE
    >> ELSE
    >> BREAK
    >>
    >>
    >>
    >> END
    >>
    >> --Close the Cursor and free the system resources by deallocating it.
    >> CLOSE Wip_Boards
    >> DEALLOCATE Wip_Boards
    >>
    >>
    >>
    >>GO
    >>SET QUOTED_IDENTIFIER OFF
    >>GO
    >>SET ANSI_NULLS ON
    >>GO
    >>
    >>
    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center