Simulating Constants Using User Defined Functions

By Guest Authors on 24 February 2002 | 2 Comments | 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.

Discuss this article: 2 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

Error 823 Torn Page (3 Replies)

Free SQL Database Tool Repair (8 Replies)

Where conditions (0 Replies)

How to resolve internal fragmentation? (2 Replies)

Two select statements same table (4 Replies)

Pivot table with two columns to pivot (2 Replies)

CURRENT_DATE trouble (1 Reply)

How to install Microsoft SQL Management Studio? (1 Reply)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -