Passing entire rows to SQL CLR function/sproc

Question

I have a process where I need to perform some complex string manipulation on a SQL table containing columns like Forename, Surname, Address1 etc

To perform this data manipulation I have setup various SQL CLR C# functions but now I would like to pass the entire row to one CLR function or sproc.

What is thye best way to do this?

I'm thinking of creating a Table Valued Paremeter then creating a SQL CLR SPROC to accept the data but will the data be READ ONLY or can I perform data manipulation in C# and return a new data set?

Any ideas?

Answer 1

You cannot pass a TVP (Table Valued Parameter) to a SQLCLR procedure or function. They are defined in the database and the .NET code does not know anything about them.

If you are going to pass in all fields to this function then this function would most likely be specific to this table so why not just pass in each field as a separate input parameter?

You have a couple of options on the output:

1. If you only have a single value to return, then create a scalar function that might be used as follows:

   UPDATE tbl  
   SET tbl.Field = MyCLRFunction(tbl.Field1, tbl.Field2, tbl.Field3, tbl.Field4)  
   FROM dbo.Table tbl  
   WHERE tbl.Field5 = SomeValue;
   

2. If you need to pass back multiple values (but assumed to still be a single row), then create a table-valued function that might be used via CROSS APPLY as follows:

   UPDATE tbl  
   SET tbl.Field6 = fnc.FieldA,  
       tbl.Field7 = fnc.FieldB,  
       tbl.Field8 = fnc.FieldC,  
   FROM dbo.Table tbl  
   CROSS APPLY MyCLRFunction(tbl.Field1, tbl.Field2, tbl.Field3, tbl.Field4) fnc  
   WHERE tbl.Field5 = SomeValue;
   

Technically this does not require SQLCLR over a regular T-SQL function, but depending on what manipulation(s) are being done, it might be faster in SQLCLR.