[ACCEPTED]-Simulate enums in TSQL?-tsql

Accepted answer
Score: 13

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

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

In C#, by 7 default every enum value corresponds to 6 an integer, starting from 0. You can even 5 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 4 LINQtoSQL also supports this. If you have 3 an enum-typed property and your database 2 column is an integer type, conversion is 1 automatic.

Score: 3

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

Score: 3

You could use a CASE statement.

To create an enum-esque 6 example from a resultset, you could do something 5 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 4 something like a SWITCH statement. Put it 3 in your stored procedure so you don't have 2 to write the same code over and over. Hope 1 this helps.enter code here

Score: 2

In PostgreSql I just use VARCHARs with constraints 9 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 8 for simulating ENUMs, because they are not 7 descriptive enough. With the above schema 6 I can see right away what possible values 5 it receives, and I also get the meaning 4 of those values right away. I also get type-safety, since 3 I can't insert invalid values in that column.

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

Score: 2

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

Score: 1

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

Score: 1

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

Score: 1

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

Score: 1

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

More Related questions