CODEWITHSUNDEEP
sql-servert-sql
MatthewMartin
MatthewMartin

Reputation: 33143

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

Upvotes: 16

Views: 25129

Answers (9)

Ronald Wildenberg
Ronald Wildenberg

Reputation: 32094

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.

Upvotes: 14

Luke
Luke

Reputation: 513

You could use a CASE statement.

To create an enum-esque example from a resultset, you could do something like this:

SELECT
    FirstName,
    LastName,
    CASE JobTitle WHEN 0 THEN 'Software Developer' WHEN 1 THEN 'Software Architect' WHEN 2 THEN 'President' ELSE 'Staff' END AS 'Job Title',
    Salary
FROM
    Employees

You basically run an integer through something like a SWITCH statement. Put it in your stored procedure so you don't have to write the same code over and over. Hope this helps.enter code here

Upvotes: 3

user4903
user4903

Reputation:

The easiest way to do this in Transact-SQL is with a CHECK Constraint. You can read more about it here or here. This constraint is also available in PostgreSQL and Oracle.

Upvotes: 1

Vasu Balakrishnan
Vasu Balakrishnan

Reputation: 1771

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/

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415725

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.

Upvotes: 1

M.Turrini
M.Turrini

Reputation: 738

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!

Upvotes: 1

ahains
ahains

Reputation: 1912

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.

Upvotes: 2

Alexandru Nedelcu
Alexandru Nedelcu

Reputation: 8069

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

Upvotes: 2

Arvo
Arvo

Reputation: 10570

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.

Upvotes: 3

Related Questions