C# CLR stored procedure with table-valued parameter

Question

I have SQL Server stored procedure with a table-valued parameter

CREATE TYPE T_WORD AS TABLE
(
   SWC_Index INT IDENTITY,
   SWC_Value VARCHAR(MAX)
)

CREATE PROCEDURE SP_LOG 
    @i_msg             VARCHAR(4000) ,      
    @i_word        T_WORD READONLY   
AS
BEGIN
   SET  IMPLICIT_TRANSACTIONS  ON

   declare @i int
   SET @i = 1

   while (@i <=(SELECT COUNT(*) FROM @i_word))
   begin
       INSERT INTO LG_REPORT
       values(@i_msg,(select SWC_Value from @i_word where SWC_Index =  @i))
   end

   IF @@TRANCOUNT > 0
      COMMIT
END 

And I need to write a C# CLR wrapper that simply executes this procedure in a new connection.

C# code looks like this

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace SQLCLR
{
    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void exec_SP_LOG(Object i_msg, Object i_word)
        {
            using (SqlConnection connection = new SqlConnection("context connection=true"))
            {
                SqlCommand Command = new SqlCommand();                
                SqlParameter i_msgParam = new SqlParameter("@i_msg", SqlDbType.VarChar);                
                SqlParameter i_wordParam = new SqlParameter("@i_word", SqlDbType.Structured);
                i_wordParam.TypeName = "T_WORD";

                i_msgParam.Value = i_msg;                
                i_wordParam.Value = i_word;

                Command.Parameters.Add(i_msgParam);                
                Command.Parameters.Add(i_wordParam);

                Command.CommandText = "exec SP_LOG @i_msg, @i_word";
                Command.Connection = connection;

                connection.Open();

                Command.ExecuteNonQuery();
                connection.Close();
            }
        }
    }
}

After adding the dll to SQL Server and creating procedure

CREATE PROCEDURE [dbo].[exec_SP_LOG]         
    @i_msg             sql_variant ,
    @i_word        sql_variant
AS 
EXTERNAL NAME [SQLCLR].[SQLCLR.StoredProcedures].[exec_SP_LOG]
GO

I try to execute this function

declare @typ1 T_WORD
insert into @typ1(SWC_Value) values('djhgfj')
insert into @typ1(SWC_Value) values('dfhdf')
exec exec_SP_LOG 't1', @typ1

However I get the following error

Msg 206, Level 16, State 2, Procedure exec_SP_LOG, Line 0
Operand type clash: T_WORD is incompatible with sql_variant

It looks like something is wrong with datatype of table-valued parameter in C#.

The question is how to pass table-valued parameters properly in C# and therefore what is the proper datatype to be used for calling this extended procedure from SQL Server.

Any help is greatly appreciated.

Answer 1

There are quite a few things that need to be addressed in this code:

1. The overall design seems to fit less into a Microsoft SQL Server world and more into an RDBMS like PostgreSQL, or even Oracle (and maybe others?). It looks like you are trying to make SQL Server work like one of these other systems (or at least expect that it does), but it doesn't. I am saying this because you should spend some time reading about the various datatypes, transaction handling, and set-based vs iterative/cursor-based approaches.

2. how to pass table-valued parameters properly in C#

   Well, you do have i_wordParam set correctly to SqlDbType.Structured. And you have i_wordParam.TypeName set correctly to T_WORD. Just FYI, setting the TypeName is only required for ad hoc queries, not when calling stored procedures via CommandType.StoredProcedure.

   So if you had either a DataTable, SqlDataReader, or method that returned IEnumerable<SqlDataRecord> in the C# code already, then you could pass that along to SP_LOG with how you currently have that interaction set up.

3. what is the proper datatype to be used for calling this extended procedure from SQL Server.

   The issue (the main issue, at least) is that you cannot pass TVPs to SQLCLR objects. As stated in the MSDN page for CREATE PROCEDURE:

   • Table-valued or cursor data types cannot be used as parameters.

   TVPs are not Data Types: they are Table Types. User-Defined Table Types are used to create Table Variables. So while you do have the .NET Object Type mapping to the T-SQL SQL_VARIANT datatype correct, a SQL_VARIANT cannot be assigned a Table Variable. This is why you got the Operand type clash: T_WORD is incompatible with sql_variant error.

4. As @Damien_The_Unbeliever already pointed out in a comment on the question, you really shouldn't use the sp_ prefix for user objects as that is reserved for the system and causes the master database to be checked first for that object.

5. Unless you have a very explicit reason for doing so, I wouldn't use SET IMPLICIT_TRANSACTIONS ON. You should start a transaction explicitly via BEGIN TRAN. If a transaction was already started prior to this proc being called (i.e. @@TRANCOUNT would be > 0 at the beginning of this proc) then the COMMIT at the end would cause an error as the value of @@TRANCOUNT would be less at the end of the proc than at the beginning.

   If the reason for using Implicit Transactions is to not start a transaction if @i_word is empty, you can just exit the proc by adding the following at the beginning:

   IF (NOT EXISTS(SELECT COUNT(*) FROM @i_word))
   BEGIN
     RETURN;
   END;
   

6. You don't have any error checking or ROLLBACK logic. You should be using something along the lines of:

   BEGIN TRY
   
     BEGIN TRAN;
   
     ... one or more SQL Statements
   
     COMMIT;
   
   END TRY
   BEGIN CATCH
   
     ROLLBACK;
   
     ;THROW; -- introduced in SQL Server 2012
   
     ---- Remove "THROW" and use the following if on SQL Server 2008
     -- DECLARE @ErrMessage NVARCHAR(4000) = ERROR_MESSAGE();
     -- RAISERROR(@ErrMessage, 16, 1);
     -- RETURN;
   
   END CATCH;
   

7. Regarding while (@i <=(SELECT COUNT(*) FROM @i_word))

   If you needed a WHILE loop (you don't; more on that in a moment) then you don't want to do the COUNT(*) in the loop as it will be evaluated each time.

8. Regarding the structure of the WHILE loop:

   You don't ever increment @i, hence you have an infinite loop. If you needed the WHILE loop, it would require SET @i += 1; if using SQL Server 2008 or newer, or SET @i = @i + 1; if using SQL Server 2005.

9. There is no need for a WHILE loop in the first place. Table-Valued Parameters are just that: tables (albeit Table Variables, but still). Hence, they can be JOINed, etc. The following is a much more efficient approach:

   INSERT INTO LG_REPORT
     SELECT @i_msg, tmp.SWC_Value
     FROM   @i_word;
   

10. When inserting, you really should specify the field names. Hence INSERT INTO LG_REPORT (column1, column2) instead of just INSERT INTO LG_REPORT. Not specifying the field names allows for the situation where you add a field to LG_REPORT and forget to update this INSERT and it fails. Or maybe you don't have the fields in the right order. Stuff like that.

11. While it won't cause errors, you (everyone, really) should get in the habit of ending each query with a semi-colon.