Hi Gurus
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegroups.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/pr...005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx
> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegroups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David
Showing posts with label runtime. Show all posts
Showing posts with label runtime. Show all posts
Monday, March 26, 2012
dynamic procedure creation - possible?
Hi Gurus
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegrou ps.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx
> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David
|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).
|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegro ups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David
sql
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegrou ps.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx
> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David
|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).
|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegro ups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David
sql
dynamic procedure creation - possible?
Hi Gurus
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegroups.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx
> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegroups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David
I am trying to develop an application around SQL server. The app allows
the user to input a where clause at runtime, which the application runs
against. The only way the app can use the input where clause is by
building an SQL statement including the where clause, and then calling
sp_executeSQL against it. This is inefficient; ideally I would like to
compile the SQL statement (as it will be used multiple times) - but how
can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
from inside another statement (it must be done explicitly with a
hardcoded where statement).
Is there any wat I can improve on building the statement and calling
sp_executeSQL every time I need to run it?"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169494423.824216.35130@.38g2000cwa.googlegroups.com...
> Hi Gurus
> I am trying to develop an application around SQL server. The app allows
> the user to input a where clause at runtime, which the application runs
> against. The only way the app can use the input where clause is by
> building an SQL statement including the where clause, and then calling
> sp_executeSQL against it. This is inefficient;
Why do you think that? SQL Server will cache and reuse SQL queries sent
using sp_executesql, especially if you use parameter markers in the query.
See
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/aa175244(SQL.80).aspx
> ideally I would like to
> compile the SQL statement (as it will be used multiple times) - but how
> can I do this? I cannot call CREATE PROCEDURE, as that cannot be done
> from inside another statement (it must be done explicitly with a
> hardcoded where statement).
> Is there any wat I can improve on building the statement and calling
> sp_executeSQL every time I need to run it?
>
No. That is really a perfectly fine thing to do. Consider using parameter
markers in sp_executesql to get query plan reuse and reduce compilations.
However if the volume of these dynamic queries is not great, or the cost of
each query to execute is large, then plan reuse may not be important or even
desirable.
David|||Thanks very much for that David.
The queries don't have parameters - they are always exactly the same
every time they are run (the user may change the where clause, but this
will be a very infrequent event). However, there could be up to 255 of
these queries (and where clauses), which could potentially all get
called (in series) every time a row is inserted into a certain key
table. A row is inserted in the key table possibly several times a
second (worst case).|||"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1169495992.357049.50780@.m58g2000cwm.googlegroups.com...
> Thanks very much for that David.
> The queries don't have parameters - they are always exactly the same
> every time they are run (the user may change the where clause, but this
> will be a very infrequent event). However, there could be up to 255 of
> these queries (and where clauses), which could potentially all get
> called (in series) every time a row is inserted into a certain key
> table. A row is inserted in the key table possibly several times a
> second (worst case).
>
Ok, 255 different queries isn't much at all. I would just let SQL Server
cache them.
David
Monday, March 19, 2012
Dynamic images at runtime
I have a report that comes with one main image and 15 other images that needs to be visible as needed at fixed position to the main image. Adding all the images is easy but managing the visibility is not working. What is the best way to do it? I am looking for sample but have come up empty.
When using a url, you can leverage the expression property to have the dynamic image location.
Or are you meaning a dynamic number of images? That would be tough - probably need to build the rdl yourself w/ .net
|||Thanks for replying I don't have the option to use url, the application will an exe, factory safety related one main image and 15 smaller images added as needed in fixed position relative to the original image.|||it's still hard to figure out exactly what you want to do here. can you give more details in order to better help people answer your question?|||What I am trying to do turns out to be complicated but I have found two samples from the Reporting services news group posted by Microsoft employee Bruce Johnson [MSFT] of how to create it. I have an image and I need to add 15 smaller images to that image at runtime. The two samples I found are Simulated Dynamic Visibility.rdl and ConditionallyDisplayAnImage.RDL. These are doing what I have in mind I could not load the rdl into VS Team suites so I have to create mine based on the content of these two files. I had assumed this should be simple task but it comes with tricks which I just found today. Thanks again for your help.|||This problem is solved the main part was a cursor yea but my code was due by the close of business, the numeric values representing the images were passed by a cursor in a stored proc which is added to the expression. It works in my box, I will know by Monday if it blows. If you know a better solution please let me know.
Friday, February 17, 2012
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
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
Subscribe to:
Posts (Atom)