Unfortunately, "stored procedure" is never CLR. By definition, this is a SQL procedure. The code being executed in CLR can only create the code of the SP, trigger its execution, pick up the results of its execution from the SQL server.
And SQL does not have general enumeration types. (Some SQL-based systems do have it, such as
MySQL.) Please see, for example:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c7abe3c1-cd48-4ddf-9ce2-b28d0ffb25f6/enum-in-sql-server?forum=transactsql[
^],
http://stackoverflow.com/questions/1434298/sql-server-equivalent-to-mysql-enum-data-type[
^].
(The answers referenced above suggest some work-around approach similar to SQL, but it will hardly help you much.)
So, at best, you probably can represent your .NET enumeration types with some SQL integer type, to store your 1, 2, 3 values. Working with your .NET enumeration types will require
type casting.
Another approach would be modeling some enumeration types in SQL. It will have two tables: one table for types, another table for enumeration members (for all types together; each enumeration member record will be linked to its enumeration type by a foreign key). For each enumeration member, you can store its underlying integer type, name (string) and, optionally, ordered number. But how to identify .NET enumeration type and member in those tables? By name.
[EDIT] On the .NET side, you can get all required information using reflection. [END EDIT]
Some performance cost, no doubt, albeit very minor compared to the cost of doing SQL queries themselves.
I am not sure that both approaches are good enough to bother. In both, the problem is support, the need to change .NET types and SQL data in synch. Essentially, you have to store not only the application data on SQL tables, but also part of its
metadata, which breaks certain isolation between the database and client code. Not sure if there can be something better. Maybe you should better review the whole idea…
—SA