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]

Re: How do I use constants in stored procedures?

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('G reen')


On Wed, 3 Dec 2003 17:54:41 -0000, "Robin Tucker"
<idontwanttobes pammedanymore@r eallyidont.com> wrote:
[color=blue]
>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?
>
>[/color]