Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Simulate enums in TSQL?

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.
like image 897
MatthewMartin Avatar asked Jun 08 '09 11:06

MatthewMartin


1 Answers

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 = 0,
    SecondEnumValue = 1
}

...

// 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.

like image 55
Ronald Wildenberg Avatar answered Sep 28 '22 01:09

Ronald Wildenberg