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