I have a stored procedure that retrieves customer data from one table and calls 2 UDFs to perform a calculation on data obtained from joined tables for accounting data.

The calculation needs to be performed for every row based on matching criteria of previous records. The query returns very few rows (5 at the most).

So far, I am getting accurate results.

My question is, should I try to accomplish the same thing by calling a stored procedure instead of UDFs?

I am looking for the preferred method.

Any comments or suggestions would be appreciated.

From my experience, if you find a way that works, go with it until it quits working. Then worry about fixing it. It's not a bad thing to want to do things the "right" way, just be careful not to create too much work for yourself.

commented: Good attitude. Make it work first, make it aesthetic second. :-) +1

I agree with Fortinbra, to a certain extent. From a pragmatic standpoint, go with what you know best, are more comfortable with, and can deliver in a timely fashion.

That being said, I try like crazy to avoid UDFs. I use them when the SQL for a single statement gets too darn ugly, and when I have to return one scalar value per affected column per row, based on data from the row. You can use stored procedures for that sort of thing too, so there's really no advantage that I have seen to using one over the other.

For application development, I use stored procs for just about everything else, including simple selects. It helps secure the data and minimizes the temptation on evil developers to use dynamic SQL. ;-)

For DBA work, I use lots of scripts and the GUI (because I'm lazy).

My company uses a stored procedure to create dynamic queries, inserts and updates. I keep trying to push them to do a schema update, optimize it a bit. right now it looks horrendously like an Oracle fast port. Which is exactly what it is.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.