Table Valued Methods on Type
I amd really getting into the use of UDTs as a way of
packaging up SQL CLR code. I'm also looking into implementing an array type so I
can do code like
declare @s
dbo.SQLArray
set @s.AddKeyword('sas')
set @s.AddKeyword('dog')
Fo this to be realy useful you would want to be able to select from your
array.
select *
from
@s.ListValues()
Unfortunately thats not possible, you can't have a table valued function on a
type. You get 1 of two errors one complaining that you have to alias the table
and columns of the valued function and the other saying you can't alias the
column in table valued function. On reading BOL it states that you can't do
this.
So your other option is to add a static method to your type and have it take
an instance of the type from which you return the list.
declare @s
dbo.SQLArray
set @s = 'simon,sabin'
select *
from dbo.ListValues(@s) as
List
I am still looking into the true impact of having such code in terms of
performance and supportability, but I am sure it would be of use in some
situations to make more readable code even if it doesn't
perform great.
-