Feature Request: Generate the equivalent of 'Enums' for use in SQL scripts...

[GoogleCodeExporter]

The benefit of using Enums needs an equivalent on the SQL side of things.

This way someone could write their stored procedures/views/etc to use  form 
such as

SELECT * FROM Customer WHERE CustomerTypeID = enum.CustomerType_TierOne();

Unfortunately MS has not came to the conclusion that we need something like 
this and they don't really offer us anything in the form of an enum mechanism - 
not even something as simple as constants (I don't think there's any way to use 
defaults for this purpose)... 

To address this 'need', I did the following on a prior project...

First I wrote a small stored procedure to maintain a meta-data table that would 
contain the same information as the .CSP properties file would have where those 
tables that should have an Enum generated are called out. The downside to this 
is that for a database 'enum' to get generated... the user has to add their 
table name into this script... Approaching this from NetTiers itself would 
alleviate this step since the user would already be doing so in the .CSP file 
to get their C# enums...

Next I wrote a second stored procedure that then uses the control/meta-data
in the table to iterate over each table and build a user-defined function for 
each value in each table...  The function just simply returns a static integer 
value (just like calling .GetHashCode() would on a C# Enum)...

This is a bit on the 'hackish' side - but it has been well received for the 
most part by developers on the project I implemented it on...  In lieu of and 
until real enums or constants become available - this will have to make do...

I've attached my scripts to give you an idea of how I approached it from the 
SQL-only aspect...

I'd love to see NetTiers provide something of this nature! Call it a challenge 
to the team!

Original issue reported on code.google.com by bh...@questis.com on 21 Sep 2010 at 8:18

Attachments:

• 00_EnumFunctions_TableList.sql
• 01_EnumFunctions_Generator.sql

[GoogleCodeExporter]

Original comment by bniemyjski on 21 Sep 2010 at 8:22

• Changed state: Investigating

[GoogleCodeExporter]

Original comment by bniemyjski on 21 Sep 2010 at 8:23

• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

Reviewing old items I logged to follow-up and check for progress...

Original comment by bh...@questis.com on 8 Jun 2011 at 5:22