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.


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

T-sql - we created Message from app1 and trying to disable from app2 (10h)

SQL select Top 10 records for unique combination of two columns (1d)

SSRS Report Sorting with Grouping Issue (2d)

ORA-01476: divisor is equal to zero (2d)

Create new columns based on min and max values of a record with multiple rows (2d)

Memory Required for Reporting Services 2022 (2d)

Backup sql server large db on cloud (2d)

Selecting x columns but only displaying y columns (2d)

- Advertisement -