Enums in SQL 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Enums in SQL 2005

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2006-07-26 : 04:05:49

I have some query's who look like this

SELECT * FROM tblProcess WHERE Status = '3018A87A-1468-4FE1-8B70-996963BB0C43'

I reads bad. Can I define some global ENUMs?

Like
@@StateAccomplished = '3018A87A-1468-4FE1-8B70-996963BB0C43'
@@StateAsked = '96A52B7F-DADF-4C93-B6B0-05E3359B86BE'

So I can make the query like:

SELECT * FROM tblProcess WHERE Status = @@StateAccomplished

@@StateAccomplished is a static value.

@@ is just me putting something on the screen.

Henri
~~~~
There's no place like 127.0.0.1

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-26 : 04:23:27

I dont think that there is something called as ENUM in the SQL Server

For the Round about solution you can create the looktable and using join retrive the values from that table.

Something like this


Create Table TblLookup
(
Code Varchar(200),
Description Varchar(8000)
)

Insert tblLookup 
Select 'StateAccomplished', '3018A87A-1468-4FE1-8B70-996963BB0C43'
Union all 
Select 'StateAsked','96A52B7F-DADF-4C93-B6B0-05E3359B86BE'

-- Then your query 
SELECT tblProcess.* FROM tblProcess Inner join
(Select Description From TblLookup Where Code = 'StateAccomplished') as f On f.Description = TblProcess.Status

Chirag

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2006-07-26 : 04:34:24

It's a cute workaround! I used something similar like:

DECLARE @StateAsked UNIQUEIDENTIFIER
SET StateAsked = someguid

SELECT * FROM tblProcess WHERE StateId = @StateAsked

However. I really would like to use some ENUM type of solution. I can't find any but a vaguely remember that I read something about it (for SQL 2005).

Henri
~~~~
There's no place like 127.0.0.1

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-26 : 05:03:25

Check out here is one more workround :)

http://staff.interesource.com/james/oct05/sql_constants.htm

Chirag

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2006-07-26 : 05:09:36

Ahhh, thx, much better already. I will make some small changes, but the concept is good! Thx for spending time on this

, if a have made the final solution, I will post it.

Henri
~~~~
There's no place like 127.0.0.1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources