Re: SQL CLR table as function parameter
Greg Using xml (tables/parameters) in sql databases should be your final last ditch choice, they perform woefully badly. What exactly are you trying to do that can't be done with 1. Repeatable calls to the same prepared sp. 2. Sql in clause generated in code? 3. Sqlcmd query? 4. Bulk loading data. Xml in databases will bite you in the ass later. .02c Davy When all you have is a hammer, every problem looks like a nail. I feel much the same way about xml -Original Message- From: Greg Keogh g...@mira.net Sender: ozdotnet-boun...@ozdotnet.com Date: Thu, 30 Jun 2011 09:45:16 To: g...@greglow.com; 'ozDotNet'ozdotnet@ozdotnet.com Reply-To: ozDotNet ozdotnet@ozdotnet.com Subject: RE: SQL CLR table as function parameter Chaps, in future, I think I'll consider feeding XML into user-defined procs and functions (especially those written in managed code). I personally have only needed to do the most simple parsing of XML in T-SQL using the SelectNodes lookalike techniques, but I knows there's a lot more you can do. I'll probably be forced to return to this subject soon. In the meantime I hard-coded a few CLR functions with different numbers of parameters to suit our needs. The general purpose method taht I planned can come later. - Greg
RE: SQL CLR table as function parameter
Using xml (tables/parameters) in sql databases should be your final last ditch choice, they perform woefully badly. It's ok, no problem. I didn't explain earlier that what's actually happening: A stored proc has to get a variable number of key/value pairs out of a table, then join them in a certain way and put an MD5 hash on them. My colleague said that selecting the pairs and turning them into XML is trivial, so he gives the XML to my managed code function and I do all the messy work and give him back the string. So the XML is not long lived, it's just a way of getting variable numbers of parameters to me. I'm quite happy with the technique. parameters parameter name=Foo value=Bar/ parameter name=Wing value=Ding/ : /parameters Greg
RE: SQL CLR table as function parameter
That can work quite well but be careful if you're using the .nodes() method in T-SQL to provide the table to join to. It doesn't have cardinality estimates (actually it guesses 10,000 rows) and can end up with some seriously badly performing query plans. Often we have to use it to extract the data into a temporary table and then join to it. (Wish it wasn't so). Regards, Greg From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Jamie Surman Sent: Wednesday, 29 June 2011 4:13 PM To: ozDotNet Subject: Re: SQL CLR table as function parameter Personally I tend to do this kind of stuff by passing the parameter as xml. In your SQL you can then join to the xml as though it were a table. _ From: Greg Keogh g...@mira.net To: ozDotNet ozdotnet@ozdotnet.com Sent: Wed, 29 June, 2011 5:17:40 Subject: RE: SQL CLR table as function parameter Oh oh! http://msdn.microsoft.com/en-us/library/ms131103.aspx has some bad news: Table-valued parameters are user-defined table types that are passed into a procedure or function and provide an efficient way to pass multiple rows of data to the server. Table-valued parameters provide similar functionality to parameter arrays, but offer greater flexibility and closer integration with Transact-SQL. They also provide the potential for better performance. Table-valued parameters also help reduce the number of round trips to the server. Instead of sending multiple requests to the server, such as with a list of scalar parameters, data can be sent to the server as a table-valued parameter. A user-defined table type cannot be passed as a table-valued parameter to, or be returned from, a managed stored procedure or function executing in the SQL Server process. So I have to find some other convenient way of getting variable numbers of parameters into a managed function. Is there a T-SQL equivalent of String.Join(...) that easily joins many strings together with a separator, I could possibly accept that a proxy variable length parameter. Greg
Re: SQL CLR table as function parameter
Hi Greg, We use a user defined table type, something like: ALTER FUNCTION [dbo].[MyFunction] ( @MyParameter [dbo].[MyUserDefinedTableType] READONLY ) On Wed, Jun 29, 2011 at 12:32 PM, Greg Keogh g...@mira.net wrote: Folks, has anyone written a SQL Server user-defined function (not a proc) that takes a TVP (Table-Valued Parameter)? ** ** I’m writing a function that needs to take a variable number of strings as arguments. It’s unlikely I can define a SQL function that takes the equivalent of “params string[] value”, so it seems logical to pass a table instead with all the strings in a column. The docs encourage me by saying this: ** ** Table-valued parameters are a new parameter type in SQL Server 2008. Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters. ** ** However, the coding syntax for the CLR code and CREATE FUNCTION have me flummoxed. Has anyone done this, or know the syntax? I’m still searching the web without success. ** ** Greg
Re: SQL CLR table as function parameter
Never done it but I think the CLR type in the function will have to be a System.Data.DataTable. Passing XML is another option. On Wed, Jun 29, 2011 at 12:59 PM, Shane Fitch shane.fitch...@gmail.com wrote: Hi Greg, We use a user defined table type, something like: ALTER FUNCTION [dbo].[MyFunction] ( @MyParameter [dbo].[MyUserDefinedTableType] READONLY ) On Wed, Jun 29, 2011 at 12:32 PM, Greg Keogh g...@mira.net wrote: Folks, has anyone written a SQL Server user-defined function (not a proc) that takes a TVP (Table-Valued Parameter)? I’m writing a function that needs to take a variable number of strings as arguments. It’s unlikely I can define a SQL function that takes the equivalent of “params string[] value”, so it seems logical to pass a table instead with all the strings in a column. The docs encourage me by saying this: Table-valued parameters are a new parameter type in SQL Server 2008. Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters. However, the coding syntax for the CLR code and CREATE FUNCTION have me flummoxed. Has anyone done this, or know the syntax? I’m still searching the web without success. Greg
RE: SQL CLR table as function parameter
Oh oh! http://msdn.microsoft.com/en-us/library/ms131103.aspx has some bad news: Table-valued parameters are user-defined table types that are passed into a procedure or function and provide an efficient way to pass multiple rows of data to the server. Table-valued parameters provide similar functionality to parameter arrays, but offer greater flexibility and closer integration with Transact-SQL. They also provide the potential for better performance. Table-valued parameters also help reduce the number of round trips to the server. Instead of sending multiple requests to the server, such as with a list of scalar parameters, data can be sent to the server as a table-valued parameter. A user-defined table type cannot be passed as a table-valued parameter to, or be returned from, a managed stored procedure or function executing in the SQL Server process. So I have to find some other convenient way of getting variable numbers of parameters into a managed function. Is there a T-SQL equivalent of String.Join(...) that easily joins many strings together with a separator, I could possibly accept that a proxy variable length parameter. Greg
