Simulate enums in TSQL?

Question

I recently accidently wrote a really ugly stored proc where I wished I had enums,

Eg.

CREATE PROCEDURE Proc_search_with_enum @user int, @account_category {enum}

I understand that SQL 2000 doesn't have enums as a first class language construct, which coding conventions do you use to simulate enums or otherwise address the same issue?

Or am I'm I doomed to just using VARCHAR and IF @account_category='cat1'?

EDIT: T-SQL and C# are the client languages.

EDIT: Thanks all! Lots of good advice, I wish I could accept several answers, I've voted everyone up-

Summary of answers

• Lean on C#'s enum by using int. Good for C# client code, makes TSQL client code less readable.
• Use Char/Varchar. Bad for C# client code (not good for localization), makes TSQL code more readable.
• Use parameter checking code to restrict the parameter, or use a constraint on a table column or a foreign key if the parameter is going to be inserted into a table.

Answer 1

You could take a look at the answer to this question. As far as I know, enum types are not part of SQL Server.

Anyhow, it's better to use an integral (INT, TINYINT, ...) type for your enums than a string type. Usually the enum types in your programming language of choice correspond better to integers than to strings.

In C#, by default every enum value corresponds to an integer, starting from 0. You can even cast between them, so this is valid code:

public enum MyEnum
{
    FirstEnumValue,
    SecondEnumValue
}

...

// Assuming you have opened a SqlDataReader.
MyEnum enumValue = (MyEnum) reader["account_category"];

And LINQtoSQL also supports this. If you have an enum-typed property and your database column is an integer type, conversion is automatic.

Answer 2

In PostgreSql I just use VARCHARs with constraints attached ...

CREATE TABLE movie_clip (        
    type VARCHAR(40) NULL CHECK(type IN ('trailer', 'commercial')),
);

#=> insert into movie_clip (type) values ('trailer');
INSERT 0 1
#=> insert into movie_clip (type) values ('invalid value');
ERROR:  new row for relation "movie_clip" violates check constraint "movie_clip_type_check"

#=> \d movie_clip
....
"movie_clip_type_check" CHECK (type::text = ANY (ARRAY['trailer'::character varying, 'commercial'::character varying]::text[]))

I don't like using numeric types for simulating ENUMs, because they are not descriptive enough. With the above schema I can see right away what possible values it receives, and I also get the meaning of those values right away. I also get type-safety, since I can't insert invalid values in that column.

For more details on constraints for Postgres, see here: http://www.postgresql.org/docs/8.1/static/ddl-constraints.html

Answer 3

Sometimes CHAR type is more usable than INT - fixed size char doesn't take much storage room and you can see "enumerated" values directly in database fields. No difference from code side, but big advance while working directly with SQL tools.

Answer 4

I generally prefer to call the parameter @account_category_code and make it a CHAR(3), if there are only a few enum values and they can all be expressed cleanly with three letters. Then the domain is enforced with a check constraint. If there are more than a handful of values (more than around 4 or 5) then I would generally switch to a tinyint/smallint called @account_category_type_id and have a domain table to reference for it. We don't have a hard and fast rule in my organization, but I find this works well.

Answer 5

Be exteremely careful in using CHAR type for enum, and avoid it at all if you want your app/db go internationally.
Don't confuse the DATA with it's presentation: like the words should suggest, an enum(eration) is a number, it's description is a totally different business. To make it short, using a CHAR variable/field for an enum, you tie yourself to a particular language for their description: you can forget about internazionalization, for instance. Can you imagine what the word "Weltmeisterschaft" means, in which language and - moreover - how many different ways of writing it wrong there can be? Actually, a (tiny)int has actually the downside of values not intrisecally auto-descriptive: I didn't say it's the perfect solution!

Answer 6

In SQL, you enumerate items by putting them in a table. Create a lookup table for your account categories and have this parameter accept the primary key for the new table.

Answer 7

You could create a view to simulate the Enum. See this article http://www.olegsych.com/2008/07/t4-template-for-generating-sql-view-from-csharp-enumeration/