Dynamic CLR Table-Valued Function

Is it possible to create a CLR Table-Valued Function in SQL 2005 in which th
e
fields can be defined at runtime? In other words, table-valued function must
define a table definition in the [SqlFunction] attribute, which makes the
definition of the columns you are returning from the function static. How ca
n
I overcome this limitation and at runtime define the columns to return?
What I want to achieve is something similar to the in-line Table-Value
Function available in SQL 2000 and SQL 2005, but now using the CLR."examnotes" <Fernando@.discussions.microsoft.com> wrote in
news:89B31AF7-70BD-4CF4-B319-D25FEE310058@.microsoft.com:

> Is it possible to create a CLR Table-Valued Function in SQL 2005 in
> which the fields can be defined at runtime?
[snip]

> What I want to achieve is something similar to the in-line Table-Value
> Function available in SQL 2000 and SQL 2005, but now using the CLR.
Not as far as I know.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels,
Thanks for the answer. By the way, congratulations on your work. I have your
book since it was released and it has been a great reference for me.
Fernando
"Niels Berglund" wrote:

> "examnotes" <Fernando@.discussions.microsoft.com> wrote in
> news:89B31AF7-70BD-4CF4-B319-D25FEE310058@.microsoft.com:
>
> [snip]
>
> Not as far as I know.
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>|||Hello Fernando,

> Is it possible to create a CLR Table-Valued Function in SQL 2005 in
> which the fields can be defined at runtime?
Yes and I don't think so.
I don't think so: The typical TVF pattern isn't going to accomodate that
-- Understand that SQL Server really expects the return value of any CLR
function to come form System.Data.SqlTypes. At least that's what I've figure
d
out so far. There's not SqlTable type in that namespace, so I don't see how
you're doing to do this any conventional way.
My work-around would be have the TVF return a single column of XML.

> In other words,
> table-valued function must define a table definition in the
> [SqlFunction] attribute, which makes the definition of the columns you
> are returning from the function static.
This is true if and only if you are using Visual Studio to deploy the functi
on
because Visual Studio consumes that attribute to write the CREATE FUNCTION
statement. Fire up profiler and what the deployment process, you'll see it.

> How can I overcome this
> limitation and at runtime define the columns to return?
Write a code generator that generates the TVF, because -- short of that --
you're not going to get around the fact that a T-SQL TVF requires a static
table definition. Or return XML.

> What I want to achieve is something similar to the in-line Table-Value
> Function available in SQL 2000 and SQL 2005, but now using the CLR.
I don't see any easy ways to do that, at least today.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Kent,
Thanks for the great ideas to workaround this issue. The code generator
probably will work better for my case as I intend to join results from
multiple Table-Valued Functions.
Thanks!,
Fernando
"Kent Tegels" wrote:

> Hello Fernando,
>
> Yes and I don't think so.
> I don't think so: The typical TVF pattern isn't going to accomodate that
> -- Understand that SQL Server really expects the return value of any CLR
> function to come form System.Data.SqlTypes. At least that's what I've figu
red
> out so far. There's not SqlTable type in that namespace, so I don't see ho
w
> you're doing to do this any conventional way.
> My work-around would be have the TVF return a single column of XML.
>
> This is true if and only if you are using Visual Studio to deploy the func
tion
> because Visual Studio consumes that attribute to write the CREATE FUNCTION
> statement. Fire up profiler and what the deployment process, you'll see it
.
>
> Write a code generator that generates the TVF, because -- short of that --
> you're not going to get around the fact that a T-SQL TVF requires a static
> table definition. Or return XML.
>
> I don't see any easy ways to do that, at least today.
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Kent Tegels <ktegels@.develop.com> wrote in
news:b87ad74f0cb8c7ba687dccef80@.news.microsoft.com:

> Hello Fernando,
> This is true if and only if you are using Visual Studio to deploy the
> function because Visual Studio consumes that attribute to write the
> CREATE FUNCTION statement. Fire up profiler and what the deployment
> process, you'll see it.
[snip]

> Write a code generator that generates the TVF, because -- short of
> that -- you're not going to get around the fact that a T-SQL TVF
> requires a static table definition. Or return XML.
The issue is not so much the table definition in itself, but the
FillRowMethod method, which has to have the same output param definition as
the table definition.
Niels|||"examnotes" <Fernando@.discussions.microsoft.com> wrote in
news:B7466A77-02A2-42DC-8583-8AC96C49D670@.microsoft.com:

> Niels,
> Thanks for the answer. By the way, congratulations on your work. I
> have your book since it was released and it has been a great reference
> for me.
>
Thanks so much!
Niels|||Fernando (Fernando@.discussions.microsoft.com) writes:
> Is it possible to create a CLR Table-Valued Function in SQL 2005 in
> which the fields can be defined at runtime? In other words, table-valued
> function must define a table definition in the [SqlFunction] attribute,
> which makes the definition of the columns you are returning from the
> function static. How can I overcome this limitation and at runtime
> define the columns to return?
You really should not be able to. A table-valued function is something
that can be used in a query:
SELECT a.col1, a.col2, b.col3, ...
FROM tbl t
JOIN tblfunction (@.par1, @.par2) f ON t.keycol = f.somecol
A query is static, and all parts in it must be known when the query
is compiled. SQL Server has deferred name resolution, so you might be
able to create the procedure if an object is missing. But if the query
does not compile, when it's time to execute it you get an error.
After all, with your dynamic TVF, what is going to happen if it does
not return somecol in its result set?

> What I want to achieve is something similar to the in-line Table-Value
> Function available in SQL 2000 and SQL 2005, but now using the CLR.
You cannot do inline-functions with the CLR. CLR TVF are akin to
multi-statement functions in T-SQL. An inline function is in fact
not a function at all; it is a macro which is expanded into the query,
and the optimizer builds the query plan for the expanded query. It
goes without saying that is not going to happen with a CLR function.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx