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

Question

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

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 2

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)
    )