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]
go

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')
go

Group Values:

insert into Constant   (ConstantName, ConstantValue, ConstantGroup)
values ('STATES_ALABAMA','AL','GRP_STATES')
go
 
insert into Constant   (ConstantName, ConstantValue, ConstantGroup)
values ('STATES_ALASKA','AK','GRP_STATES')
go

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 (jbkayne@yahoo.com)
 --*  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--
    as
 
    begin
    return (select ConstantValue from Constant where ConstantName=@ConstantName)
    end
GO

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.

declare @CONST_STATES_ALABAMA
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.