How do I use constants in stored procedures?

[Robin Tucker]

I have several instances of "magic number" variables (tinyints). In my
program, I have assigned an enumeration to make the meaning clear, such as:

enum Condition {

Green = 0,
Yellow,
Red
}

In my database, one of the tables contains a "Condition" field (tinyint),
which stores the number 0, 1 or 2. However, in my Stored Procedures I am
having to use magic numbers as follows:

SELECT * From Nodes Where Condition = 1

(to select all nodes with yellow condition)

Obviously, meaning is obfuscated here. I would rather use constants but not
have to re-define them in every stored procedure I use.

I there any way to do this?

[Bruce Loving]

You could create a UDF

create function uf_Condition( Status as varchar(6)) as integer
set uf_condition = case Status when 'Green' then 0
when 'Yellow' then 1
when 'Red' then 2
end

then
SELECT * From Nodes Where Condition = uf_Condition('Green')
On Wed, 3 Dec 2003 17:54:41 -0000, "Robin Tucker"
<id*************************@reallyidont.com> wrote:

I have several instances of "magic number" variables (tinyints). In my
program, I have assigned an enumeration to make the meaning clear, such as:

enum Condition {

Green = 0,
Yellow,
Red
}

In my database, one of the tables contains a "Condition" field (tinyint),
which stores the number 0, 1 or 2. However, in my Stored Procedures I am
having to use magic numbers as follows:

SELECT * From Nodes Where Condition = 1

(to select all nodes with yellow condition)

Obviously, meaning is obfuscated here. I would rather use constants but not
have to re-define them in every stored procedure I use.

I there any way to do this?