|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
User defined enumerated data typeHi there,
I'm new to SQL server and I'm looking at this database on which a VB application is built upon in this company. I'm wondering if there is a way of defining my own enumuerated data type in SQL server so I don't have to use a table/hardcoding in the code??? Version of SQL is 8.0, if it makes any difference. Any comment is much appreciated. Thanks > defining my own enumuerated data type Can you define this better fo us non-hardcore VB programmers?Sorry, I didn't make myself clear and I'm already very rusty with VB...
For example, say the status of an order could have only 3 values: Active, Invoiced, and Deleted. One can create a table OrderStatus with PK of type int with values 1,2,3 and a short description to represent the 3 statuses. The Order table will need to have 1 field order_status which references table OrderStatus. When coding, it's either checking the order status against hardcoded value 1, 2 or 3 (which make the code unreadable) or it's joining the OrderStatus table and check the short description of the order status with hardcoded value of the short description which makes the code a little more readable. If I can create a enumerated data type called orderStatus with 3 values and reference them as something like orderStatus.active, orderStatus.invoiced and orderStatus.deleted and the order_status field in the Order table uses this enumerated data type, then the code could be much easier to read. I'm wondering whether and how I could create a enumerated data type within SQL server and use it within stored procedures as well as in VB. Thanks. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > defining my own enumuerated data type > > Can you define this better fo us non-hardcore VB programmers? > > > No, there are no complex data types like this in SQL Server 2000. Maybe you
can do something like this with the CLR in SQL Server 2005. I'm not sure why you can't use a foreign key relationship (to enforce data integrity) and leave the pretty names for these properties for the display side. Comments make code more readable than any choice of "data type properties" would be, IMHO. Show quote "MapleLeaf" <MapleL***@discussions.microsoft.com> wrote in message news:A2D2EE8B-71A3-470D-B95F-A3460E0A9C4C@microsoft.com... > Sorry, I didn't make myself clear and I'm already very rusty with VB... > > For example, say the status of an order could have only 3 values: Active, > Invoiced, and Deleted. One can create a table OrderStatus with PK of type > int > with values 1,2,3 and a short description to represent the 3 statuses. The > Order table will need to have 1 field order_status which references table > OrderStatus. When coding, it's either checking the order status against > hardcoded value 1, 2 or 3 (which make the code unreadable) or it's joining > the OrderStatus table and check the short description of the order status > with hardcoded value of the short description which makes the code a > little > more readable. If I can create a enumerated data type called orderStatus > with > 3 values and reference them as something like orderStatus.active, > orderStatus.invoiced and orderStatus.deleted and the order_status field in > the Order table uses this enumerated data type, then the code could be > much > easier to read. > > I'm wondering whether and how I could create a enumerated data type within > SQL server and use it within stored procedures as well as in VB. > > Thanks. > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> > defining my own enumuerated data type >> >> Can you define this better fo us non-hardcore VB programmers? >> >> >> Thank you...
The use of FK would certainly work but the availabilty of enumerated data type could serve a somewhat different purpose. With a more meaningful data type, I don't need to rely on a person to document the code. The code itself is self-explanatory. Thanks again for the reply. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > No, there are no complex data types like this in SQL Server 2000. Maybe you > can do something like this with the CLR in SQL Server 2005. > > I'm not sure why you can't use a foreign key relationship (to enforce data > integrity) and leave the pretty names for these properties for the display > side. > > Comments make code more readable than any choice of "data type properties" > would be, IMHO. > > > "MapleLeaf" <MapleL***@discussions.microsoft.com> wrote in message > news:A2D2EE8B-71A3-470D-B95F-A3460E0A9C4C@microsoft.com... > > Sorry, I didn't make myself clear and I'm already very rusty with VB... > > > > For example, say the status of an order could have only 3 values: Active, > > Invoiced, and Deleted. One can create a table OrderStatus with PK of type > > int > > with values 1,2,3 and a short description to represent the 3 statuses. The > > Order table will need to have 1 field order_status which references table > > OrderStatus. When coding, it's either checking the order status against > > hardcoded value 1, 2 or 3 (which make the code unreadable) or it's joining > > the OrderStatus table and check the short description of the order status > > with hardcoded value of the short description which makes the code a > > little > > more readable. If I can create a enumerated data type called orderStatus > > with > > 3 values and reference them as something like orderStatus.active, > > orderStatus.invoiced and orderStatus.deleted and the order_status field in > > the Order table uses this enumerated data type, then the code could be > > much > > easier to read. > > > > I'm wondering whether and how I could create a enumerated data type within > > SQL server and use it within stored procedures as well as in VB. > > > > Thanks. > > > > "Aaron Bertrand [SQL Server MVP]" wrote: > > > >> > defining my own enumuerated data type > >> > >> Can you define this better fo us non-hardcore VB programmers? > >> > >> > >> > > > > type, I don't need to rely on a person to document the code. The code Well, like I said, if you can't rely on the person to document the code (or > itself > is self-explanatory. at least document the database documentation, you do do that right?), how can you rely on them to choose appropriate and self-explanatory names for the properties of the enumerated data type? A >> Active, Invoiced, and Deleted. One can create a table OrderStatus with PK of type INTEGER with values 1,2,3 and a short description to represent the 3 statuses. << I would not bother with a seocnd table. This code not going to changeover time or be used anywhere else, so use: CREATE TABLE Orders ( .. order_status CHAR(3) DEFAULT 'act' NOT NULL CHECK (order_status IN ('act', 'inv', 'del')), ...); This version is shorter than a 4-byte INTEGER, requires no indexes, etc. > CREATE TABLE Orders It's not smaller than a SmallInt which is SQL92 compliant or a TinyInt (this is > ( .. > order_status CHAR(3) DEFAULT 'act' NOT NULL > CHECK (order_status IN ('act', 'inv', 'del')), > ..); > > This version is shorter than a 4-byte INTEGER, requires no indexes, > etc. a SQL Server newsgroup after all). Thomas  |
|||||||||||||||||||||||
Other interesting topics