SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Simulating Constants Using User Defined Functions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 02/24/2002 :  18:19:38  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
This article comes from Jeremy Kayne. He writes "the ability to globally store and access constant values in SQL Server has always been at the top of my wish list. Since this is not a functionality that is currently available, I have created a simple solution that I would like to share." Thanks for the article Jeremy!

Article Link.

hopkihc
Starting Member

USA
8 Posts

Posted - 04/12/2004 :  10:11:19  Show Profile  Reply with Quote
Interesting article, but I'm thinking it solves only part of the problem. You're still hardcoding a string (in this case, 'STATES_ALABAMA'), so you still need to make sure you've spelled the string correctly in your function call. What if I do:

fnc_GetConstant ('STATES_ALABMA')

I'll get a NULL back, where my intent was to get a state abbreviation. The compiler won't catch this error, and I'll get the wrong result. Debugging will be tricky (especially if I've been looking at T-SQL for several hours straight).

For me, there's no appreciable difference between:

WHERE state=dbo.fnc_GetConstant('STATES_ALABAMA')

and

WHERE state='AL'

In fact, I've now incurred a slight performance hit by forcing a lookup of the Constants table.

If I need to refer to constants, especially for domains with a few values (such as "Status"), I'll create functions for each constant value I need (often returning them as a user-defined type):

CREATE FUNCTION dbo.udfStatusActive()
RETURNS udtStatus AS
BEGIN
RETURN ('ACTIVE')
END

CREATE FUNCTION dbo.udfStatusInactive()
RETURNS udtStatus AS
BEGIN
RETURN ('INACTIVE')
END

True, this can lead to a lot of functions, but they perform well and they provide an extra measure of confidence when compiling. If I need to update the constant value, I alter the function.

My two pennies.



John Hopkins
Augusta, GA
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/14/2004 :  09:30:06  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
John --

I completely agree with you; I believe 1 function with no arguments per constant is the way to go ... I've even played with having the owner of the functions be "const" or something like that, since you always have to fully qualify the function with the owner anyway.

When i think of constants, I think "they can only be altered by changing the source code", which I would not equate to changing the data in tables. Changing values in your data to change app behavior to me would be more "settings" as opposed to "constants". Of course, this could also be an issue of definition of terms.

The main problem IMHO with the technique presented is that it might lead to users taking that approach to return lookup values as opposed to doing a much more efficient JOIN.


- Jeff
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000