Article: Simulating Constants Using User Defined Functions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Simulating Constants Using User Defined Functions

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-24 : 18:19:38

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

8 Posts

Posted - 2004-04-12 : 10:11:19

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-14 : 09:30:06

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources