Sunday, February 13, 2005

Technical Problem with UDT (User Defined Types) in SQL

 Originally posted on:

Can anyone help me with this? no prizes will be awarded...

SQL Server 2000 allows you to define user types based on the exiting types (excluding CURSOR). This is then supported throughout SQL Server and T-SQL, so that you can use this to define Fields, Parameters in SPs and more. Enterprise manager supports this as well, by loading these custom types into the design view of tables.

What do I need UDTs for? well in our application we have many database tables and fields, we have found more than once a need to change some field definition, causing a cascading change in all relevant tables/views/functions/stored procedures, and of course the functions in the VB 6 data layer that we use to invoke these objects.

Recently we thought using UDTs is a good solution, since (as far as we can figure out) you would only need to change the defintion at the basic type level, and the change will bubble up (assuming you wrote your code to use it, i.e. wrote your SP to accept this type by type name).

All the basic tests on the database level look good, however we are stumped by one major (to us) issue: How do we make this work from the VB layer? we use all over the command object to interact with our stored procedures (we try to do most everything with SP), however the command parameter object only accepts members of the Type Enum of the ADO which obviously does not support my custom type.

Looking around the net I have been unable to find a solution, I suppose the only way to do it, is to change it in both places, both the DB and VB layers (maybe using constants to limit the amount of work to do).

Does anyone have an idea of how to accomplish this? is there maybe some other way to do this? is using UDTs even a good idea - or are we missing the whole point?


No comments: