How do I use enums in TSQL without hard coding magic number all over my SQL scripts/procs?

Question

We have enums in our C# code:

public enum JobStatus
{        
  Ready = 0,      
  Running = 1,        
  Cancelling = 2,
}

These values are also stored in database fields, and we have lots of TSQL (mostly stored procs, as well as some batches and SSIS) that also processes the data:

SELECT TOP 1 @JobSID = JobSID
FROM Job 
WHERE Status = 0 /* JobStatus.Ready */
ORDER BY SubmitDate ASC

CREATE TABLE ImportCrossEffect(
    /* lots deleted */      
    Source tinyint 
      DEFAULT 1 NOT NULL -- 0: Unknown (default), 1:Imported, 2:Keyed
) 

How do I avoid hard coding the “magic numbers” in the TSQL?
How do I remove the risk of the enumerations not matching on the C# and TSQL sides?

(I have included the C# tag, as I would like solution that “single sourced” the definitions of the enums on both the C# and TSQL sides)

---

Updates:

We don't have tables in the database with the Enum names in them, the values are just stored in tiny int columns.

I was hoping for something like a SQL pre-processor that would "expand" all the enum to there "magic value".

Answer 1

You can always pass the value from the enumeration into the stored proc/command that you are trying to execute. This way, you never have to worry about the enumerations in the database.

If you want to store the enumerations in the database, then I suggest you create a view (possibly titled with your enumeration), like so:

create view JobStatus
    select 0 as Ready, 1 as Running, 2 as Cancelling

You can then access/join on the view if you need it to.

Note, the query optimizer treats any reference to the above as a constant scan/scalar operation, not a table scan, so you aren't incurring the reads that would occur if you were accessing an actual table.