Is there any way to make a CLR stored procedure that accepts an array style set of data? I want to make a stored procedure that accepts to parameters of type int, and then one more that is an array of name/value pairs. Is it possible to do something like this?
Yes, there is. In fact there is a sample at http://www.codeplex.com/MSFTEngProdSamples called "Array Parameter" which you can browse or download along with the other engine database samples. This sample contains code for passing an array to a CLR stored procedure using a CLR UDT. But be careful in SQL Server 2005 as you are limited in size to 8000 bytes. A future version of SQL Server is expected to relax that constraint. The other option which doesn't have that problem is to encode your data in XML and pass it using an XML parameter and rehydrate the objects in the CLR stored procedure.
No comments:
Post a Comment