SQL CLR issue: CREATE PROCEDURE failed because parameter counts do not match

Question

I did check this but the problem doesn't appears to be with mismatch of type.

I'm trying to use CLR to convert back a file from blob data. Below is the c# code converted to dll and stored using assembly.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;


namespace CLRProcedures
{
   public class WriteFile
    {
        [SqlFunction]
        public static SqlInt32 WriteToFile(SqlBytes binary, SqlString path, SqlBoolean append)
        {
            try
            {
                if (!binary.IsNull && !path.IsNull && !append.IsNull)
                {
                    var dir = Path.GetDirectoryName(path.Value);
                    if (!Directory.Exists(dir))
                        Directory.CreateDirectory(dir);
                    using (var fs = new FileStream(path.Value, append ? FileMode.Append : FileMode.OpenOrCreate))
                    {
                        byte[] byteArr = binary.Value;
                        for (int i = 0; i < byteArr.Length; i++)
                        {
                            fs.WriteByte(byteArr[i]);
                        };
                    }
                    return 1;
                }
                else
                   return 0;
            }
            catch (Exception ex)
            {
                return -2;
            }
        }
    }
}

SQL queries are as below :

CREATE ASSEMBLY FileOutput from 'c:\dlls\CLRProcedures.dll' WITH PERMISSION_SET = SAFE 

CREATE PROCEDURE FileOutput
@file varbinary(max),
@filepath nvarchar(4000),
@append bit,
@message int OUTPUT    
AS    
EXTERNAL NAME FileOutput.[CLRProcedures.WriteFile].WriteToFile

This throws error : 'CREATE PROCEDURE failed because parameter counts do not match.'

I've re-verified from here if there is type mismatch but there isnt one. Where could I be going wrong ? I tried to change the return type of c# code but same issue.

Thank you

Answer 1

You are ignoring what the error message is saying:

CREATE PROCEDURE failed because parameter counts do not match (emphasis added)

The parameter "count", not any particular parameter "type", is the issue. You have 3 input parameters in the method signature but are specifying 4 parameters in the CREATE PROCEDURE statement.

Your options are:

1. Do as Naji is suggesting and add a 4th parameter, declared as out in the C# code, and change the return to setting that message variable, which would match the 4 parameters you have defined.
2. Or, you can keep the C# code as-is and get that return value the same way that you would a regular T-SQL stored proc return value:

   DECLARE @ReturnValue INT;
   
   EXEC @ReturnValue = dbo.FileOutput 0x1234, N'path', 1;
   
   SELECT @ReturnValue;
   

3. Or, you can even switch to having this be a function by simply changing the T-SQL CREATE object statement to be:

   CREATE FUNCTION dbo.FileOutput
   (
     @File VARBINARY(MAX),
     @FilePath NVARCHAR(4000),
     @Append BIT
   )
   RETURNS INT
   AS EXTERNAL NAME ...
   

   And use as follows:

   DECLARE @ReturnValue INT;
   
   SET @ReturnValue = dbo.FileOutput(0x1234, N'path', 1);
   
   SELECT @ReturnValue;
   

   (Technically, as a scalar function that returns an INT / Int32, you can even execute it using the same stored procedure syntax as shown in option 2 above)

ALSO:

1. If you are creating a procedure, you should decorate the C# WriteToFile method with SqlProcedure instead of SqlFunction.
2. Rather than iterating over the entire binary value as a byte[], the SqlBytes type offers a Stream property that is an actual stream that is a more efficient way of reading that value. Just copy binary.Stream to the file stream and be done :-)
3. Always specify a schema name (for schema-bound objects), whether in CREATE statements, SELECT, EXEC, etc.

For more info on working with SQLCLR in general, please visit: SQLCLR Info

Answer 2

You need to add the OUTPUT parameter to the WriteToFile function to return value

[SqlFunction]
public static void WriteToFile(SqlBytes binary, SqlString path, SqlBoolean append,out SqlInt32 message)
    {
        try
        {
            if (!binary.IsNull && !path.IsNull && !append.IsNull)
            {
                var dir = Path.GetDirectoryName(path.Value);
                if (!Directory.Exists(dir))
                    Directory.CreateDirectory(dir);
                using (var fs = new FileStream(path.Value, append ? FileMode.Append : FileMode.OpenOrCreate))
                {
                    byte[] byteArr = binary.Value;
                    for (int i = 0; i < byteArr.Length; i++)
                    {
                        fs.WriteByte(byteArr[i]);
                    };
                }
                message = 1;
            }
            else
                message = 0;
        }
        catch (Exception ex)
        {
            message = -2;
        }
    }

for more details : CLR Stored Procedures