I faced problem that I had many lookup tables like ProductType, ClientType etc. and wanted to refer to their values in code to base some logic on them. This is quite common.

I am talking about something like following:

SELECT @ClientType = ClientType, -- other columns
    FROM Client
    WHERE Id = @ClientId
IF (@clientType = 'SOMETYPE')
    -- do something

It is quite ok, but practice problem happened if you spread string constants representing lookup values over your T-SQL routines. This means that you have your ‘SOMETYPE’ constant defined on many places which is always not good for many reasons. If you would be in C# or other type-safe language you could afford to use constant static variables representing each lookup value and define their string values only at one place.

Such pseudo-hybrid code would look like this:

SELECT @ClientType = ClientType, -- other columns
    FROM Client
    WHERE Id = @ClientId
IF (@clientType = ClientType.SOMETYPE)
    -- do something

This is of course syntactically not correct in T-SQL but using CLR static variable might be good option.  There are few solutions how to implement “static” variables in T-SQL. Among better solutions belongs usage of UDF described e.g. here or very limited solution with CONTEXT_INFO described here.

I would like to describe other solution which uses SQL CLR User-Defined Type as static class for string constants. I am not going to describe what is CLR UDT and how to develop and deploy them, you can read it msdn.

First step is to write and deploy following UDT:

[Serializable()]
[SqlUserDefinedType(Format.UserDefined, MaxByteSize = -1)]
public struct ClientType : IBinarySerialize, INullable
{
    /// <summary>
    /// TypeA value
    /// 
    public static string TypeA { get { return "TYPEA"; } }
    ///
    /// TypeB value
    /// 
    public static string TypeB { get { return "TYPEB"; } }
    public void Read(BinaryReader r)
    {
        throw new NotImplementedException("Don't persist this UDT, it holds only constants.");
    }
    public void Write(BinaryWriter w)
    {
        throw new NotImplementedException("Don't persist this UDT, it holds only constants.");
    }
    public bool IsNull
    {
        get { throw new NotImplementedException(); }
    }
    public static ClientType Null
    {
        get
        {
            throw new NotImplementedException("Shouldn't have null value.");
        }
    }
    public override string ToString()
    {
        throw new NotImplementedException("Don't persist this UDT, it holds only constants.");
    }
    public static ClientType Parse(SqlString s)
    {
        throw new NotImplementedException("Don't persist this UDT, it holds only constants.");
    }
}

Second step is to use this UDT in your code:

SELECT @ClientType = ClientType, -- other columns
    FROM Client
    WHERE Id = @ClientId
IF (@clientType = ClientType::TypeA)
    -- do something

Now you have “strongly typed” lookup value used in your T-SQL code. Trick is that you can call static members of UDT from T-SQL by using “::” and completely avoid using string constants for lookups throughout your SQL code.

Jakub Dvorak