[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Enums in Sql-Server

Posted on 2004-11-01
7
Medium Priority
?
670 Views
Last Modified: 2012-05-05
Hi,
   Can we have enums in sql-server? If not, is there any equivalent of enum in sql server.
I'm working on tsql and need to use enum in a particualar scenario in a stored procedure.
Thanks in Advance
0
Comment
Question by:answer_me
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
7 Comments
 
LVL 8

Expert Comment

by:sigmacon
ID: 12463264
SQL server doesn't support enums directly - at least not like MySQL (that's the only place where I know about enums ...), but you can create a column as text and put a check constraint on it:

CREATE TABLE SomeTable (
    SomeEnumColumn varchar(10) not null
        constraint CkSomeEnumColumn check (
            SomeEnumColumn in ('Value1', 'Value2', 'Value3')
        )
)

Hope that helps. If not, please post more specifics about what you are trying to do in the stored proc.
0
 
LVL 10

Author Comment

by:answer_me
ID: 12463835
This is one of feasible approach i already know but i don't want to go this way. coz there is an overhead of maintaing a table and insertions for this.
0
 
LVL 8

Accepted Solution

by:
sigmacon earned 252 total points
ID: 12464260
OK, compared to

CREATE TABLE SomeTABLE (
    ...
    SomeEnumColumn ENUM('Value1', 'Value2', 'Value3') NOT NULL
    ...
)

What kind of table overhead are you talking about? I may misunderstand what you are referring to with ENUM? In my example above, the column is part of whatever table you already have and does not need to be created extra:

CREATE TABLE YourTableDefinition (
    ...
    SomeEnumColumn varchar(10) not null
        constraint CkSomeEnumColumn check (
            SomeEnumColumn in ('Value1', 'Value2', 'Value3')
        ),
    ...
)
0
 
LVL 11

Assisted Solution

by:rdrunner
rdrunner earned 248 total points
ID: 12466222
Hello...

I would implement an enumeration like this.. I would create one "master enum" table which contains ALL enums for ALL fields in your Database.

The master enaum table would look like this

ID,Value,EnumCode,Valid
1,1,1,true
2,2,1,true

As an aditional feature you could create an aditional table which could hold all laguages for the same enumeration. This would allow for a good localisation support.
ID,EnumID,Text,Language
1,1,"Male","EN"
2,1,"männlich","DE"
3,2,"Female","EN"
3,2,"weiblich","DE"

etc..

You can also create a function that will validate if the current code is valid.


0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question