Skip Navigation LinksHome > Categories > Code from a Category

How to Simulating Constants Using User Defined Functions


User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 1254
Add Date: 12/22/2013
In this article we will learn about simulating constants using 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.
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.

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

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.


Happy Programming!!!

Post a Comment
Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below: 		(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.