Even though TSQL does not allow constant declarations per se, there are several ways to handle constant values in SQL Server databases.
The simplest one is to document special values (apart from keeping a list on a piece of paper which drowns in the sediments on your desk) is to list them in the Description property of a column, although this is not what databases are made for.
Next is a check constraint on columns which only allow specified values and cause INSERT/UPDATE statements to fail if the new value does not match the constraint.
My favorite method is to have a table of constant values with a numeric identifier, a programmatic identifier for use in a programming language, and a natural-language string to be displayed to the user.
My application dbscript supports this method and generates code to link C# code to the values stored in the database. I described this mechanism earlier here and here.
As an example, take a simple table describing command execution states:
ID | Name | ProgID |
10 | Command Pending | Pending |
20 | Command Running | Running |
30 | Command Done | Done |
This table can be represented in C# as
public class ExecutionState { public const int Pending = 10; public const int Running = 20; public const int Done = 30; }
One disadvantage remains though: if you want to use the constant value in TSQL (for example, in a stored procedure), you still have to know the numeric value, or retrieve the numeric value by its programmatic identifier. In both cases, there is no way to check automatically that the values or identifiers actually exist.
Yet another method came to my mind recently: create a single-row view with the column names representing the constant identifiers and the fields containing the constant values:
CREATE VIEW V_Execution_State AS SELECT 10 AS Pending, 20 AS Running, 30 AS Done
This way, constant identifiers can be checked by TSQL, and values easily assigned, like this:
DECLARE @state INT SELECT @state = Pending FROM V_Execution_State
By the way, this is post # 200 😉
Pingback: Generating Views for MS SQL Server Constants « devioblog