T-SQL - repetitive code

Depends a little on the specific situation.  Yes, if it's sort of "calculation" or "manipulation of data" then a function could be a good fit.  If it's more data retrieval based, then sometimes a view can be a different / better way to modularize that.  If it's code that is only repeated in the current proc then sometimes a CTE is a good fit.

As far as the choice between Functions or Procs, here's a good summary of the differences to help guide a choice.

• Difference between Stored Procedure and Function in SQL Server

---

»bp

Good reading but the most important note is missing: Functions in SP are degrading the performance significantly because SQL Engine does not look into their code during the SP compilation.

One single function call on a single line is OK in most of the cases but once you use function calls inside the query then the SQL engine almost ignores them. This may result in the poor query plan creation. Functions also do not appear in the I/O statistics. Thus the number of logical reads performed by a query containing functions is useless.

So please think twice before replacing the repetitive code by function calls. Replace it by Stored Procedure call if possible.

Scalar functions do have a "black box" effect and these should be used carefully (e.g. avoided in where clauses).

Table Valued Functions (TVF) have a very different effect, but they don't suit every situation.

So I would think twice before using scalar functions calls, but have no hesitation suggesting table valued functions. Particularly if a TVF avoids row-by-agonizing-row cursor operations.

I really likes Bill Prew's summary by the way; Views are often overlooked and avoiding repetition is one of the best reasons for using CTE's

As poul said scalar function would make query very slow.

table value function is good option since it work bit differently than scalar.

but they can't handle complex calculation.

if you are using same code in multiple locations, it would be better to build an common stored procedure.

so it will make code readable as well as ease of maintenance.

regarding performance of stored procedure.
it will be precompiled and plan Will be stored in cache.

so it will no make big difference if your filter parameters will not make any difference in query execution plan.

hope it will help

[..] dealing with long repetitive Store Procedures [..]

Select allOpen in new window

Do you have similar pieces of code repeating in your procedure?

Then you may have a model issue. You may have stored data in columns which should be normalized and should be rows. Or sometimes vice versa. Also using CTE's for temporarily normalizing tables is often an option.

The other problem maybe the code itself. Do you have a lot of conditionals and code-flow structure in it? Then refactoring to a set-based approach maybe a solution. Also using temporary tables to gather intermediate results can help to reduce code-flow.

And finally: sometimes you end up having repeating (similar) pieces of code. Cause T-SQL is not a generic programming language. Thus redundancy in production code is not necessarily bad per se.

Thanks everyone for your input!

So the code I am thinking of creating functions for are SELECT code-blocks that return custom "BeginOfWeek", "WeekToDate" results, based on specific parameters. This same code is used in numerous Sprocs.

Each may have around 10 - 15 lines of code but added together they tend to clutter the SProc and would require changing each Sproc when a change is made to the code. Using a function seems like the natural thing to do but as many have mentioned, a few stored procedures would do the same job.

Am I understanding the general consensus correctly, is a Sproc a better choice in this case?

Can you provide more context about your calendar calculus? Or even post this sproc?

Cause often it is already sufficient to use a precalculated calendar (tally) table. Also these kind of functions, when not inline-able can hit performance.

Each comment is valid and something to consider, so thank you all, very much!

pcelba, I think your last comment made it very clear, so thanks for that.

I will have to run a few tests to see which is best, thanks again all!!

Thanks for the points.

Note: To select more answers as a solution is also possible at EE.