Finding weekdays of a given month
-
Suffice to say, it is not the norm to have to use a cursor or loop. But, our system does involve multiple scientific codes and multiple dollar prorations assigned to those codes, (e.g., 20% of 50% to one sub-area of an area of an overall Project...). It is fairly sizeable and on those odd occassions, a cursor or loop is more effecient, easier to understand, (especially for new people trying to learn the system as well as business rules), and often re-useable.
To that end, Steve's email articles had one on "Replacing Cursors and While Loops" (not sure of author) and I am trying to make time to understand and implement that approach. So while I generally agree with you, these methods do exist for a reason and never being able to use them is too restrictive to me. Just my $0.02, (as said by others...).
I wasn't born stupid - I had to study.
-
What is it with you guys and loops today??
This is a freaking basic select from one table. Why do loops, inserts and reselect from a #temp table??
-
I'd like to see one of these cursors and give it a shot at a set based approach
. -
I was talking about our conversation in general on the use of Cursors, Loops and While statements....
I wasn't born stupid - I had to study.
-
I can't, unfortunately. This stuff is not for public consumption until it is release and it would be a REAL booger for me to try and make up a bunch of tables and data to reflect it. I will think about it and see if I can come up with something....
Between you and Noel, you probably could come up with something to work....
I wasn't born stupid - I had to study.
-
I know... that one was reffering to the other loop version.
-
I'd be surprised if we couldn't
. -
We have one such situation
One Invoice will have 100 or more elements upto 2000 now
We have to calculate the invoice amount based on a formula slected for each element
users will select formula steps 'A' to 'R'
For each element formula steps will be 'ABDEGHJKMNPQR' or 'AR' OR 'DFER' or any combinations
Loop will go like this Each sub procedure has complex calculatios from different accounts
I would have to say it is very difficult if not impossible to have a set based approach to
create let alone maintain and do changes for new requirements from sales and customer agreements
For each Element
BEGIN
if 'A'
Exec FormulaA @OutMessage Output, @OutAmount Output
if 'B'
Exec FormulaB @OutMessage Output, @OutAmount Output
if 'C'
Exec FormulaC @OutMessage Output, @OutAmount Output
....
if 'R'
Exec FormulaR @OutMessage Output, @OutAmount Output
UPDATE Invoice SET Amout = @OutAmount
WHERE
ID = @Id AND
Element = @Element
END
Regards,
gova -
Convert the sps to functions (try to keep the select part outside of the function) so the function can be deterministic (runs MUCH faster).
then : Update table set Amount = case when step = 'A' then dbo.fnCalcA
when....
where ?
-
That application resides on SQL Server 6.5
Still Formula step lenths are different some elements use all steps and some only a few.
Regards,
gova -
Some of us old dogs are a little slow learning new tricks !
-
Never too late to learn... the day I'll stop learning, I'll be dead.
-
Sorry to hear you're screwed
.Try converting to 9.0 when Yukon is out
. -
Mine is a bit different in that I have make the calls to find the various percents based upon the sub-areas, (or even sub-areas within different countries). So while the functions will help with the $ prorations, (I too like functions) that still does not help with the continuous callings need for each project.
It is striking me that set based would have to use so much memory in my circumstance that is would defeat its own purpose. I will think about this and see what I can come up with. Intriguing (sp).
Like I said, it is few and far between that we need to use a loop or cursor, but boy, it sure makes life easier when we do need it...
Look forward to reading and learning more next week.
I wasn't born stupid - I had to study.
-
Seems to me like a pretty simple task... and it's not 10 mb of ram that'll kill the server.
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply