| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2005-01-27 : 16:57:29
|
I have a code snippet I use in many of our SPs to adjust parameters @StartDate and @FinishDate so the BETWEEN operator will do what I want (that is, it sets both to midnight, properly.It don't matter none, but the snippet looks like:-- Setup the Start and Finish DatesSELECT @StartDate = CONVERT(VARCHAR, IsNull(@StartDate, CourseStartDate), 101) , -- Forces the startdate to Midnight of course start @FinishDate = CONVERT(VARCHAR, DATEADD(dd, 1, IsNull(@FinishDate, GETDATE())), 101) -- End Date is Midnight of Tomorrow FROM dbo.Courses WHERE CourseID = @CourseID So this is spread in about 30 procs.Maintenance is a headache if there is ever a change. Would it be better to make a couple of functions or a stored proc to handle these classes of snippets (I have several) or will that muck up the execution plan and make recompilations more likely? |
|
|
TimS
Posting Yak Master
198 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-01-27 : 18:22:51
|
| Hi SamC, I'm all about code re-reuse. I did a "little" testing on whether functions or SPs were more efficient as reusable code. I didn't check for re-comps but basically just exec'd both versions in timed loops. The function versions were superior for my tests. They certainly are more flexable in that you can use single value type functions as part of a column list. Just for maintain-ablility I would definately start replacing your duplicated snippets with either functions or SPs (but I'd go with functions) I sure hope that functions are superior because I'm developing quite a library of them. :) |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-27 : 18:59:10
|
| Functions can cause problems as they can cause row by row type query plans - depends on how you use them.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-10 : 01:42:33
|
| I hardwire these sorts of "Choose today, midnight, if blank/NULL" in the SProcs.When I have a Better Idea I use a decent editor to make global changes to the source code.That redates all the source files, which then causes them to be re-executed against their appropriate daabases.But I dunno if that helps any!Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-10 : 05:04:32
|
| I usually hardwire repetetive sql in the sprocs as well. (copy,paste,search&replace)I primarily use functions more in a "global" sense, not for code-reuse per se.In this case I might consider: fnGetMidnight(@d datetime) returns datetime...,but that fn is so trivial so I might not bother.rockmoose |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-02-10 : 10:28:17
|
| Hard wiring code snippets does take out a layer of abstraction removing the possibility of performace problems, that's true. However, for mission critical calculations I don't like to have the potential of different version out there. I would much prefer to have a single point of contact when it comes to things like calculating customer balances and things of that nature.As far as functions go and the "library" I mentioned earlier in the thread, these are almost exclusively things don't actually get used directly in sql statements (or at least not in a columns list). They prepare snippets of dynamic sql based on "array" style input parameters, formatting durations for process logging, translating a bitmask value to a series of integers for a where clause, and stuff like that. Basically I think functions are the way to go when you have these sorts of repeated tasks that can be performed with values already contained in memory, not (necessarily) for manipulation of data that will be applied to every row in a result set.Be One with the OptimizerTG |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-10 : 13:47:59
|
I agree with You TG.quote:
However, for mission critical calculations I don't like to have the potential of different version out there. I would much prefer to have a single point of contact when it comes to things like calculating customer balances and things of that nature.
Yes, and sometimes a fn will be appropriate for this task. But not always. The inherent problem with fn for this kind of calculations is that they can force sql server to row by row processing, as Nigel pointed out.rockmoose |
|
|
|
Xerxes
Aged Yak Warrior
666 Posts |
Posted - 2005-02-10 : 14:25:57
|
quote:
Originally posted by TimS
Might wish to see if this code is faster to use in your two functions.SELECT CAST(FLOOR(CAST(GETDATE() AS float))AS datetime) SELECT CAST(CEILING(CAST(GETDATE() AS float))AS datetime) Idea from http://www.devarticles.com/c/a/SQL-Server/Date-and-Time-Values-Using-SQL-Server-2000/4/Tim S
Great idea, Tim.Semper fi, Xerxes, USMC(Ret.)-------------------------------------------------------------------------Once a Marine Programmer Analyst ALWAYS a Marine Programmer Analyst |
|
|
|
|
Repetitive Code