I recon that you could do all of this in one INSERT statement - it would be a gargantuan INSERT statement, but after spending the last few minutes reading the code I think it could be done. That would be the most efficient option - however trying to explain how to go from what you have here - a very procedural step-by-step approach to a more database oriented set-based approach would take a lot of time. So, I'll just show you how to add a cursor to the existing code. But, please note that cursors should only be used as a last resort - or where such a small amount of data is being processed that the performance is not an issue. Cursors are notoriously slow because database systems are optimised for set-based operations (i.e. They like to operate on many rows of data all at once, rather than stepping through one row at at time)
See the comments in you're modified source code to see what is going on.
CREATE PROCEDURE sp_UpdateLeaders
AS
DECLARE
-- prep all variables
@YspCalc money,
@FeeCalc money,
@Fees money,
@Amount money,
@Ysp money,
@Fee1 money,
@Fee2 money,
@Fee3 money,
@Fee4 money,
@Fee5 money,
@Fee6 money,
@Number varchar(50),
@McName varchar(50),
@Client varchar(50),
@Officer varchar(50),
@FName varchar(25),
@LName varchar(25),
@Date datetime,
@Points integer,
@LoanType integer,
@Mortgage integer
-- Declare the cursor. This sets up the select statement that contains
-- the rows of data we want to iterate over. (Note: I've taken out your
-- assignments to the local variables - you'll see later why)
DECLARE bors_cursor CURSOR FOR
SELECT
TLH_1_68, TLH_1_69, TLH_1_19,
TLH_1_748, TLH_1_2, TLH_2_58,
TLH_1_420, TLH_1_869, TLH_1_2,
GRICA_1_2, GRICA_1_4, GRICA_1_6,
GRICA_1_10, GRICA_1_12, GRICA_1_8,
TLH_1_317, TLH_1_876
FROM MakeDaily53.DBO.bors
WHERE TLH_1_749 Is NULL
AND Filename NOT LIKE '%.2'
AND TLH_1_1007 IN ('645','644','642','638','636','599','598','592',
'589','587','586','582','581','580','579','565','112')
-- Now open the cursor
OPEN bors_cursor
-- Collect the first row of data into the variables that will be used
-- Note: You had two different columns being assigned to @Amount, I guess one
-- of these is incorrect.
FETCH NEXT FROM bors_cursor
INTO @FName, @LName, @LoanType, @Date, @Amount, @McName,
@Mortgage, @Ysp, @Amount, @Fee1, @Fee2, @Fee3, @Fee4,
@Fee5, @Fee6, @Officer, @Number
-- @@FETCH_STATUS tells us if the previous fetch operation went okay
-- Note: This is regardless of the number of cursors you have. It always
-- corresponds to the most recent FETCH command that was executed so make
-- sure you check it after each fetch operation. A status of 0 means
-- it went okay. So here, while we keep getting rows then go into the loop.
WHILE @@FETCH_STATUS = 0
BEGIN
-- check for existing loan in leaderboard
IF NOT EXISTS(SELECT * FROM LeaderBoard WHERE LoanNumber = @Number)
BEGIN
-- You'll recognise this code - I copied it unchanged from your post.
Print(@Number)
-- check for nulls
IF @Fee1 = Null
SET @Fee1 = 0
IF @Fee2 = Null
SET @Fee2 = 0
IF @Fee3 = Null
SET @Fee3 = 0
IF @Fee4 = Null
SET @Fee4 = 0
IF @Fee5 = Null
SET @Fee5 = 0
IF @Fee6 = Null
SET @Fee6 = 0
-- concatenate client name
SET @Client = @FName + ' ' + @LName
-- ysp+rsp caluclation
SET @YspCalc = (((@Ysp - 100)/100)*@Amount)
-- additional fees are calulated differently for 1st and 2nd mortgages
IF (@Mortgage = 1)
SET @FeeCalc = ((@Fee1 + @Fee2 + @Fee3 + @Fee4 + @Fee5 + @Fee6)-1425)
IF (@Mortgage = 2)
SET @FeeCalc = ((@Fee1 + @Fee2 + @Fee3 + @Fee4 + @Fee5 + @Fee6)-275)
-- insert into leaderboard
INSERT INTO LeaderBoard (ClientName, LoanType, LoanNumber, ClosedDate, LoanAmount, YspSrp, Points, AddFees, McName, MortgageType, LoanOfficer)
VALUES (@Client, @LoanType, @Number, @Date, @Amount, @YspCalc, @Points, @FeeCalc, @McName, @Mortgage, @Officer)
END
-- Note: This fetch statement is exactly like the one just before the
-- loop starts.
FETCH NEXT FROM bors_cursor
INTO @FName, @LName, @LoanType, @Date, @Amount, @McName,
@Mortgage, @Ysp, @Amount, @Fee1, @Fee2, @Fee3, @Fee4,
@Fee5, @Fee6, @Officer, @Number
END
-- Clean up. Cursors are terribly messy
CLOSE bors_cursor
DEALLOCATE bors_cursor
-- We're done!
-- go!
GO
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|