CLR - User Defined Type - Variable Storage or Other ??

28 Jul 2006 3:13 AM

ITContractor

Greetings,

Premise:
* Several SQL Server Native data types have parameters e.g. varchar(x)
* We can, easily, create a User Defined Type for SQL Server 2005 with a CLR
- Assembly using VB or C# .Net in Visual Studio 2005.

Question:

* Is there some way to create a User Defined Type for SQL Server 2005 with a
CLR - Assembly that has at least one parameter ?? e.g. dbo.myDataType(1,2).

Examples or links would be great !

Thank you in advance.

28 Jul 2006 3:57 AM

Uri Dimant

Hi
http://www.sqlskills.com/resources/Whitepapers/SQL%20Server%20DBA%20Guide%20to%20SQLCLR.htm -
--CRL SQL Server 2005

Show quote

"ITContractor" <ITContrac***@discussions.microsoft.com> wrote in message
news:DF47DD51-87D7-4352-A128-78767F44525E@microsoft.com...
> Greetings,
>
> Premise:
> * Several SQL Server Native data types have parameters e.g. varchar(x)
> * We can, easily, create a User Defined Type for SQL Server 2005 with a
> CLR
> - Assembly using VB or C# .Net in Visual Studio 2005.
>
> Question:
>
> * Is there some way to create a User Defined Type for SQL Server 2005 with
> a
> CLR - Assembly that has at least one parameter ?? e.g.
> dbo.myDataType(1,2).
>
> Examples or links would be great !
>
> Thank you in advance.
>

28 Jul 2006 11:54 AM

Dan Sullivan

There isn't any way to make a CLR based user defined type that takes a parameter in its declaration.

Dan

28 Jul 2006 5:24 PM

Isaac Kunen [MSFT]

Do you need the parameter to be part of the type, or part of the instance?

Then normal way you create a UDT is by calling the Parse(String) method, but
you can pretty much use any static method that returns the UDT. E.g., you
could create an Array type with a variable size, and then create a
Create(int) method that returned an array with the proper size. You would
then create an array of size 5 by calling: @myarray = Array::Create(5)

The type of the type is still Array, though, not Array(5). That you can't
do.

Cheers,
-Isaac Kunen, Microsoft SQL Server

Show quote

"ITContractor" <ITContrac***@discussions.microsoft.com> wrote in message
news:DF47DD51-87D7-4352-A128-78767F44525E@microsoft.com...
> Greetings,
>
> Premise:
> * Several SQL Server Native data types have parameters e.g. varchar(x)
> * We can, easily, create a User Defined Type for SQL Server 2005 with a
> CLR
> - Assembly using VB or C# .Net in Visual Studio 2005.
>
> Question:
>
> * Is there some way to create a User Defined Type for SQL Server 2005 with
> a
> CLR - Assembly that has at least one parameter ?? e.g.
> dbo.myDataType(1,2).
>
> Examples or links would be great !
>
> Thank you in advance.
>

29 Jul 2006 12:13 AM

ITContractor

Hello Isaac,

Thank you for your time re my Question:

Basically I would want the UDT to know (allegorically speaking)
which column it is associated with in the instance.
    e.g. TableA.ColumnA AND TableB.ColumnB are type "myType"

           update TableA
                set ColumnA = 'XXXX'

           for this instance of the UDT I assume there is NO WAY to
           know within the UDT that the Parse(String) Method is acting
           on, or because of, TableA.ColumnA AND NOT TableB.ColumnB
           information, it just acts on the data.

Thanks to everyone for their input.

Show quote

"Isaac Kunen [MSFT]" wrote:

> Do you need the parameter to be part of the type, or part of the instance?
>
> Then normal way you create a UDT is by calling the Parse(String) method, but
> you can pretty much use any static method that returns the UDT. E.g., you
> could create an Array type with a variable size, and then create a
> Create(int) method that returned an array with the proper size. You would
> then create an array of size 5 by calling: @myarray = Array::Create(5)
>
> The type of the type is still Array, though, not Array(5). That you can't
> do.
>
> Cheers,
> -Isaac Kunen, Microsoft SQL Server
>
>
> "ITContractor" <ITContrac***@discussions.microsoft.com> wrote in message
> news:DF47DD51-87D7-4352-A128-78767F44525E@microsoft.com...
> > Greetings,
> >
> > Premise:
> > * Several SQL Server Native data types have parameters e.g. varchar(x)
> > * We can, easily, create a User Defined Type for SQL Server 2005 with a
> >    CLR - Assembly using VB or C# .Net in Visual Studio 2005.
> >
> > Question:
> >
> > * Is there some way to create a User Defined Type for SQL Server 2005 with
> >    a CLR - Assembly that has at least one parameter ?? e.g.
> >    dbo.myDataType(1,2).
> >
> > Examples or links would be great !
> >
> > Thank you in advance.
> >