Hi,

i feel instead of bringing one more join to sql its better to declare some variables inside the stored procedure and use it instead of bringing the enum concept forcibly in sql server.

not sure how many will agree to this. because i feel we should not sacrifice performance to readability.

Jeswanth

Yep - we have been down this path and have actually achieved it using real .Net enums (as used in our app) within SQL Serevr 2005.

We use an excellent, i.e. this is the best thing since sliced bread and cold beer!, product called StrataFrame and the use of enums within SQL Server 2005 was put to their (again excellent) support team and they came back with the following:

http://forum.strataframe.net/Topic10664-14-1.aspx

It took a little bit of messing around but, in the end, it works like a charm.

Cheers, Peter

This is what I posted on the MS forum for SQL Server in response to this column:

I think enums would be a great feature to add to SQL. I would think they would work best like a custom datatype.

Something like this:

exec sp_addenum 'Visa', 0, 'PaymentType'
exce sp_addenum 'MasterCard', 1, 'PaymentType'
exec sp_addenum 'Amex', 5, 'PaymentType'

Then you would use it in a table as:

create table Orders (
OrderID bigint not null identity(1,1),
CustomerID bigint not null,
PaymentMethod PaymentType not null
)

The underlying datatype should be a bigint - allowable to be compared to other integer values so that they could be passed in from other sources.

Then in queries like suggested:

select * from Orders where PaymentMethod in (PaymentType.Visa, PaymentType.MasterCard)

The question would be is something like this allowed?

select * from Orders where PaymentMethod = Shipping.UPS

I think it should be prohibited. Either a constant (PaymentMethod = 1) or a valid enum type (PaymentMethod = PaymentType.Visa) or a variable (PaymentMethod = @PaymentType) should be accepted.

This would hopefully at least prevent people from writing the PaymentMethod = Shipping.UPS code.

I have a small "Attribute-Value" table, and a UDF to go with it.

Then I can do SELECT * FROM Orders where PaymentType = dbo.fn_SystemInfo('VISA'). Unfortunately this results in a convertion to/from varchar which may not be efficient. But it is easy on the eye to read the code.

Your suggestion would help a lot.

Best regards

Henrik Staun Poulsen

the stress should be on performance and related topics than readability. for readability use comments in code. something like

SELECT fld1,fld2 FROM Orders where PaymentType = 1 -- VISA

Regards

Jeswanth