Using custom function in SQL statement, good/bad
-
Hello All,
I'm more familiar with programming in VFP/VB etc.. and I know how I would handle this there but not sure which is best for SS2K SPs.
Should start, can't change the data structure. The issue is getting All vs Active vs Inactive records for a given query.
Where needed there are 2 fields - Active Date and Inactive Date which I would check against the current date. So if I wanted Active records I would have something like:
ActiveField < GETDATE()
AND
( InActiveField IS NULL OR InactiveField > GETDATE() )Since I will have something like this in many SPs I was wondering if I should just create a function which returns whether a record is active by passing the Active/Inactive dates. Something like:
WHERE dbo.IsActiveRecord( ActiveField,InactiveField ) = 1
Obviously my main concern is performance but also wondering if this is the way more experienced SS devs (who can't change the structure) would deal with this.
I should add that I might be building the select and executing like:
EXECUTE @ErrorNum = sp_executesql @sqlquery
Any and all thoughts welcome.
Much Thanks,Steve Dingle
-
I use functions all the time. In our database we deal with a lot of issues with measurement and currencies that require changes on the fly to different measurements. So we normalized our databases to a specific measurement and currency. We then wrote a function to return the value based on the the client's measurement/currency requirement.
I can't say I have ever used a date field to determine if something is active.
I usually use a char(1) field called Status or Active setting the field to 'A' as a default and 'I' when inactive.
You can use a trigger or job to update status as dates roll forward and old records need to be made inactive. That way you logic will be in one place on de-activating a record if you need to change the business rules instead of scattered throughtout various procedures.
-
If your main concern is performance the a good rule of thumb is to adhere to standard T-SQL constructs wherever possible. Introducing additonal functions will only add to the overhead of the query.
That said, everyone's environment differs and what works for one might not be appropriate for another. So it all comes down to testing what works effeciently in your environment.
--------------------
Colt 45 - the original point and click interface
-
Em: Thanks for the input, good to know it's not a huge no-no to use UDFs. FWIW, I almost always use Active/Inactive fields dates when I need to know WHEN not IF. For things like contracts/accounts and the like a single field just doesn't provide enough information. Also I find them useful when I need to inactivate something in the future (like something running out in 12 months)
philcart: Yes, I suppose as always its depends. Again it is nice to know that if I handed the database over to someone with more expertise they would cringe at 1st site of it tho.
Much Thanks,Steve Dingle
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply