Simulating Constants Using User Defined Functions

By Guest Authors on 24 February 2002 | Tags: Stored Procedures , User Defined Functions

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!

The schema I use is relatively straightforward. It uses name/value pairs, and includes a column for grouping. This grouping column is used to establish simple parent/child hierarchies.
create table [dbo].[Constant] (
    ConstantName varchar(50)  not null
    , ConstantValue varchar(50)  not null
    , ConstantGroup varchar(50)  null
) on [primary]

In this example, I will be creating a list of state code constants. I begin by creating a parent record. This parent record is optional. However, grouping common constants will make life easier. Clashes are possible when a different value is desired but you want to use the same constant name. By putting similar constants together in a group and systematically naming that group’s "children", any possible clashes will be avoided and the code will be more readable.

First, I prefix the group name with 'GRP' to form the group root name. I then add a row for each state prefixing each constant name with the postfix of the constant group. This is shown below.

Root Group:

insert into Constant   (ConstantName, ConstantValue, ConstantGroup)
values ('GRP_STATES','STA','GRP')
Group Values:
insert into Constant   (ConstantName, ConstantValue, ConstantGroup)
insert into Constant   (ConstantName, ConstantValue, ConstantGroup)

In order to retrieve the constant value efficiently, I have created a simple user defined function that takes the constant name as a parameter and returns the constant value.

create function fnc_GetConstant
 --*  Type:   User-Defined Function (Public)
 --*  Name:   fnc_GetConstant
 --*  Author: Jeremy Kayne (
 --*  Desc:   Returns the value of a constant
 --*  Parameters      Description
 --*  ---------------------------------------------------------------------------
 --*  @ConstantName   Input string that contains the Constant Name
 --*  Return          The matching Constant Value
    @ConstantName varchar(50)
    returns varchar(50)
    --with encryption--
    return (select ConstantValue from Constant where ConstantName=@ConstantName)

Now you are all set to implement constants within your stored procedures. Give it a try!

select dbo.fnc_GetConstant('STATES_ALABAMA')

Results in ‘AL’

If the same UDF is called repeatedly for the same constant, or if a direct call to a UDF won't work due to syntax violations, try declaring a local variable and setting it once through the UDF.

select @CONST_STATES_ALABAMA=dbo.fnc_GetConstant('STATES_ALABAMA')

Jeremy Kayne is a "database specialist" in his current job. He has a BS from the University of Washington in mathematics and loves a good T-SQL problem.

Related Articles

Handling SQL Server Errors (5 April 2010)

Testing with Profiler Custom Events and Database Snapshots (22 June 2009)

Debugging Stored Procedures in Visual Studio 2005 (25 June 2007)

Returning Complex Data from User-Defined Functions with CROSS APPLY (11 June 2007)

Writing CLR Stored Procedures in C# - Returning Data (Part 2) (23 June 2005)

Writing CLR Stored Procedures in C# - Introduction to C# (Part 1) (6 June 2005)

Managed Data Access Inside SQL Server with ADO.NET and SQLCLR (31 May 2005)

Using CROSS APPLY in SQL Server 2005 (4 May 2005)

Other Recent Forum Posts

Oracle Database Client for Microsoft Tools (11h)

Summarize the proportions stored in the table (1d)

SQL SSMS 20 Grid Not Updating? (1d)

Problem With Joining 4 Tables With 1 Being A Reference Table (1d)

Reporting Using User Selection Dropdowns (2d)

Sum Nested Join Help (2d)

How many records for group and how many records for group members? (3d)

Group by issues (6d)

- Advertisement -