July 20, 2009 at 10:55 am
anyone who has used cursors should see the problem easily(I hope):-)
Its not erroring, just not affecting the columns,What obvious concept or code am I missing. yes, i have refreshed the table and still no changes
Declare crs_CoveragePremiums Cursor-- 2. Define the Cursor
For -- by associating
Select CP.CovID,-- it w/ a SELECT.
CP.Location,
CP.BaseRate,
A.Full_Address
From dbo.tblCoveragePremiums AS CP Left Join dbo.tblAddressAS A
On CP.CovID = A.CovID
where CP.Location Not Like '_u%'
Order By CP.PolicyBeginDate, A.Full_Address
For Update
Open crs_CoveragePremiums--3.Open the Cursor
Declare --4.Declaring 1 Vaiable per each column
@CovID int,
@Location varchar(30),
@BaseRate money,
@Full_Address varchar(max),
@extraVariable as money
print 'location name----------BaseRate------------'
FETCH NEXT FROM crs_CoveragePremiums INTO
@CovID, --these tell the cursor where to store the date in the selected row(s) of data
@Location,--must match number of columns,data type and size(which are defined above)
@BaseRate,
@Full_Address
--NEWTERM -KeySet term means the 'rows you are selecting' by the cursor
WHILE @@fetch_status = 0 BEGIN --loop will continue until the @@fetch_status = -1 or -2
If @BaseRate < 400
SET @BaseRate = @BaseRate * 1.25
print @Location PRINT @BaseRate
FETCH NEXT FROM crs_CoveragePremiums INTO
@CovID,
@Location,
@BaseRate,
@Full_Address
END
CLOSE crs_CoveragePremiums
DEALLOCATE crs_CoveragePremiums
July 20, 2009 at 10:59 am
Could you post the DDL for the table(s) and some sample data? I wouldn't be surprised if your cursor based code could be converted into a set based solution.
For help on my request, please read the first article referenced below in my signature block.
July 20, 2009 at 11:02 am
your cursor does not have an update statement...the only thing it is doing is fiddling with the variable @BaseRate i think.
did you forget to add the UPDATE to teh cursor?
WHILE @@fetch_status = 0 BEGIN --loop will continue until the @@fetch_status = -1 or -2
If @BaseRate < 400
SET @BaseRate = @BaseRate * 1.25
print @Location PRINT @BaseRate
--missing UPDATE SOMETABLE SET BASERATE = @BASERATE WHERE COVID=@COVID
FETCH NEXT FROM crs_CoveragePremiums INTO
@CovID,
@Location,
@BaseRate,
@Full_Address
END
looking at your code, i don't see you doing anything that requires a cursor...it looks like you could update with a case statement and your existing query.
Lowell
July 20, 2009 at 11:04 am
A quick try at modifying your code would go like this. You should test this in a development environment first.
update dbo.tblCoveragePremiums set
BaseRate = BaseRate * 1.25
From
dbo.tblCoveragePremiums AS CP
Left Join dbo.tblAddress AS A
On CP.CovID = A.CovID
where
CP.Location Not Like '_u%' and
CP.BaseRate < 400
July 20, 2009 at 11:05 am
here's my WAG based on your SQL:
UPDATE CP
SET CP.BaseRate = CASE
WHEN CP.BaseRate < 400
THEN CP.BaseRate * 1.25
ELSE CP.BaseRate
END
From dbo.tblCoveragePremiums AS CP
Left Join dbo.tblAddress AS A
On CP.CovID = A.CovID
where CP.Location Not Like '_u%'
AND CP.BaseRate < 400
Lowell
July 20, 2009 at 11:07 am
Lowell (7/20/2009)
here's my WAG based on your SQL:
UPDATE CP
SET CP.BaseRate = CASE
WHEN CP.BaseRate < 400
THEN CP.BaseRate * 1.25
ELSE CP.BaseRate
END
From dbo.tblCoveragePremiums AS CP
Left Join dbo.tblAddress AS A
On CP.CovID = A.CovID
where CP.Location Not Like '_u%'
AND CP.BaseRate < 400
Why the extra work with the CASE statement when you are limiting the UPDATE statement to only records where CP.BaseRate < 400?
July 20, 2009 at 11:18 am
i was kind of assuming the update was more complicated...you know 500-1000 might be a different multiplier, for example. there was no good reason for it, other than to allow for additional possibilities.
Lowell
July 20, 2009 at 11:29 am
What seems obvious after u have done it once (with your alls help that is)
relieves me, and keeps my feeling of stupidity a secret to u 'all and not my Boss. No Update statements!!!!!! thank you all! I copied and pasted the last piece of code posted to me and it worked so nicely.
At this new job my boss and another programmer use cursors like crazy, and i always heard to stay away from them unless can't find a slick trick around them in certain situations cause they obviously slow things down, but he is explicity telling me to use a Cursor on for this SP .
😀 , 🙂
July 20, 2009 at 11:35 am
Jeff Moden has a great quote on changing your mindset from row by row to "set based" thinking:
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
Lowell
July 20, 2009 at 11:37 am
adam spencer (7/20/2009)
What seems obvious after u have done it once (with your alls help that is)relieves me, and keeps my feeling of stupidity a secret to u 'all and not my Boss. No Update statements!!!!!! thank you all! I copied and pasted the last piece of code posted to me and it worked so nicely.
At this new job my boss and another programmer use cursors like crazy, and i always heard to stay away from them unless can't find a slick trick around them in certain situations cause they obviously slow things down, but he is explicity telling me to use a Cursor on for this SP .
😀 , 🙂
Just curious, but have you asked why you have to use cursors?
July 23, 2009 at 8:22 am
No, but I think he does not know anywork way around and i dont know one either. I suggested that I tyr to do what is needed with out a cusor and showed him, and he just said, " USE A cursor on this one! Its the only way to skin this cat" so although they hurt performance, I want to make it work with a cursor(cause he told me too) and then work on a much more efficient way to skin the same cat and show him. I just started working for this company, and i can see in the SPs that they are using way to many uneeded cursors. Believe me, all i have read is cursors are slow and always avoid unless, simply, you can get it done w/o one.
by the way i read there are client and server side cursors, how do u specify u want it to be server side cursor.?
July 23, 2009 at 8:34 am
The cursors you write in stored procedures are server side cursors. When you hear about client side cursors, they are talking about the application using a cursor to loop through a result set on the client.
July 23, 2009 at 8:35 am
i've been in the same situation that you are in; the lead programmer only believed that the data was right if he KNEW each row was handled.
For the first couple of years where I'm working now, everything was either cursor or {gasp} client recordset based. every suggestion I made was rejected out of hand.
it wasn't until a performance problem on posting process, which was cursoring thru half a million rows for a specific client, where the client said "cancel our contract, this is unaceptable and taking way too long" did it get addressed the right way.
at that time, when i rewrote the query to use a set based operation, and of course it performed in a sub-second instead of 15 minutes. according to him, there was no way it did the work correctly if it didn't handle all the rows...so it was days of all the developers trying to prove the logic was wrong.
Finally, after he couldn't "prove" it didn't work correctly, it got deployed, and I got a feather in my cap, but it bothered him enough that he moved on shortly after.
Since then everything was rewritten to be set based, but there is always a dragging of the feet for new ideas or concepts, no matter the situation.
Lowell
July 23, 2009 at 8:59 am
Ok , i was put on another project and back to this one and i realize
i need to be selecting in the cusor on this one and not updating. I saved the working Updating cursor to my working TSQL script folder for the future but can't alter it to just select. see we need this cursor
to select rows like A.Coverage \ A.Premium AS Rate
but there are many coverage types we need to search in the row for
and they might have from 1 to 14 coverages,depending on what the options they are paying for in 1 insurance policy which is 1 record.
so the output of this all is that for each policy# in the table
it can return a result set of 1 to 14 records per policy #.
SO in testing We are just selecting and then when all working and tweaked will make it a Make table qry inside an SP. So very new to Cursors, I need to know how the syntax of a select. ive google and looked in my cusror chapter, but something so simple and obvious to someone with cursor syntax experience, leaves me stuck!
here is my SIMPLYFIED STAB AT USING IT TO SELECT (WHICH OBVIOUSLY IS NOT WORKING) .
DECLARE Crs_Name CURSOR
FOR
SELECT
A.POLICYNr,
A.COV_I,
A.PREIMIUM_I
A.COV_II,
A.PREMIUM_II,
A.PROGRAMCODE
FROM APLICATION AS A
FOR SELECT --<<JUST A GUESS PUTTING SELECT HERE
OPEN Crs_Name
DECLARE
@POLICYNr AS CHAR(12),
@COV_I AS MONEY,
@PREMIUM_I AS MONEY,
@COV_II as MONEY,
@PREMIUM_II AS MONEY,
@PROGRAMCODE AS CHAR(2) --EXAMPLES 01,02,03
FETCH NEXT FROM CRS_Name INTO
SELECT
CASE
WHEN A.PROGRAMCODE = '01'
THEN (@COV_I / @PREMIUM_I) * 100 AS RATE
ELSE (@COV_II / @PREMIUM_II) * 100 AS RATE
END
FROM A
WHILE FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM CRS_Name INTO
@POLICYNr,
@COV_I,
@PREMIUM_I,
@COV_II,
@PREMIUM_II,
@PROGRAMCODE
END
CLOSE CRS_Name
DEALLOCATE CRS_Name
July 23, 2009 at 9:05 am
Ok , i was put on another project and back to this one and i realize
i need to be selecting in the cusor on this one and not updating. I saved the working Updating cursor to my working TSQL script folder for the future but can't alter it to just select. see we need this cursor
to select rows like A.Coverage \ A.Premium AS Rate
but there are many coverage types we need to search in the row for
and they might have from 1 to 14 coverages,depending on what the options they are paying for in 1 insurance policy which is 1 record.
so the output of this all is that for each policy# in the table
it can return a result set of 1 to 14 records per policy #.
SO in testing We are just selecting and then when all working and tweaked will make it a Make table qry inside an SP. So very new to Cursors, I need to know how the syntax of a select. ive google and looked in my cusror chapter, but something so simple and obvious to someone with cursor syntax experience, leaves me stuck!
here is my SIMPLYFIED STAB AT USING IT TO SELECT (WHICH OBVIOUSLY IS NOT WORKING) .
DECLARE Crs_Name CURSOR
FOR
SELECT
A.POLICYNr,
A.COV_I,
A.PREIMIUM_I
A.COV_II,
A.PREMIUM_II,
A.PROGRAMCODE
FROM APLICATION AS A
FOR SELECT --<<JUST A GUESS PUTTING SELECT HERE
OPEN Crs_Name
DECLARE
@POLICYNr AS CHAR(12),
@COV_I AS MONEY,
@PREMIUM_I AS MONEY,
@COV_II as MONEY,
@PREMIUM_II AS MONEY,
@PROGRAMCODE AS CHAR(2) --EXAMPLES 01,02,03
FETCH NEXT FROM CRS_Name INTO
SELECT
CASE
WHEN A.PROGRAMCODE = '01'
THEN (@COV_I / @PREMIUM_I) * 100 AS RATE
ELSE (@COV_II / @PREMIUM_II) * 100 AS RATE
END
FROM A
WHILE FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM CRS_Name INTO
@POLICYNr,
@COV_I,
@PREMIUM_I,
@COV_II,
@PREMIUM_II,
@PROGRAMCODE
END
CLOSE CRS_Name
DEALLOCATE CRS_Name
--------------
Also,
I am not sure if I can mix and match variables(@Cov_I) and columns(A.PROGRAMCODE) in my selects
I know this post is long , but even little syntax corrections would go a long way for me!
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply