Can I have a custom enumeration data-type in Sql Server?

Question

Hi folks,

In my sql code i'm passing around a bunch of magic numbers :-

AnimalType TINYINT

/*
  AnimalType can be one of the following :-
  1. Cat
  2. Dog
  3. Bird
 ....
*/

Is there anyway i could make this a custom type / enumeration. eg.

AnimalType ANIMAL

and it's constrained to contain a number between 1 <-> whatever (eg. 3 in my example above).

or constrained to strings. eg. AnimalType = 'Cat' .. etc ?

Cheers!

Edit

I know what LookUp tables are. This is not for a lookup table, but for some data passed to a number of stored procedures. Instead of passing in Magic Numbers, I wish to pass in an enumeration OR at least some contrained number (eg. numbers 1<->5), etc...

Answer 1

Enumeration is like a FOREIGN KEY to a table, but without a table.

Create a table and make a FOREIGN KEY constraint:

CREATE TABLE AnimalType (id INT NOT NULL PRIMARY KEY, name VARCHAR(50) NOT NULL)

CREATE TABLE Animal (
        id INT NOT NULL PRIMARY KEY,
        type INT NOT NULL,
        name VARCHAR(50) NOT NULL,
        CONSTRAINT FK_animal_type FOREIGN KEY (type) REFERENCES AnimalType(id)
    )

Answer 2

as per your latest edit, about needing to pass in parameters. T-SQL has the basics to to simple stuff in addition to the heavy lifting SQL. There are no enumeration data-types. If that is what you want, you can handle it in the following way:

create procedure YourProcedure
(    @param1   int
    ,@param2   varchar(5)
    ,@param3   varchar(5)
)
as

DECLARE @Value3  int

IF @param1 IS NULL OR @param1<1 OR @param1>5
BEGIN
    return 1 --error out of range param
END

IF NOT EXISTS (SELECT Value FROM LookUpTable WHERE Value=@param2)
BEGIN
    return 2 --error out of range param
END

SELECT @Value3=IntValue FROM OtherLookupTable WHERE StrValue=@param3
IF @Value3 IS NULL
BEGIN
    return 3 --error out of range param
END


--do work here


go

Answer 3

There are no enumeration types. However, you can create user defined functions to translate back and forth between INTs that you map to enumerated values.

To generate friendly names for an AnimalType based of 'INT' you could do something like this:

UDF to generate friendly names:

CREATE FUNCTION ihAnimalTypeDesc
(
    @AnimalType INT
) 

RETURNS VARCHAR(20)
AS  
BEGIN 
    IF @AnimalType IS NULL
    RETURN NULL

    DECLARE @Temp AS VARCHAR(20)

    SET @Temp = CASE @AnimalType
    WHEN 1 THEN 'Cat'
    WHEN 2 THEN 'Dog'
    WHEN 3 THEN 'Snake'
    END

    RETURN @Temp
END

A SELECT statement could uses the UDF like so:

SELECT A.AnimalName, dbo.ihAnimalTypeDesc(A.AnimalType)
FROM Animals A

Here is a UDF to return true or false if an animal is of a particular type:

CREATE FUNCTION IsCat
(
    @AnimalType INT
) 

RETURNS BIT
AS  
BEGIN 
    IF @AnimalType IS NULL
        RETURN NULL

    IF @AnimalType = 1
        RETURN 1

    RETURN 0
END

Here is an example using the above UDF. NOTE: you have to be careful with performance issue when doing this in the WHERE clause.:

SELECT AnimalName
FROM Animals
WHERE dbo.IsCat(AnimalType)

Answer 4

You can try to add something like this:

CREATE VIEW AnimalType AS
    SELECT 
     10 AS Cat
    ,20 AS Dog
    ,30 AS Bird

to use it:

DECLARE @animal INT    
SELECT @animal = Bird FROM AnimalType