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

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

Solution

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:

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.
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;
```
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:

If you are creating a procedure, you should decorate the C# WriteToFile method with SqlProcedure instead of SqlFunction.
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 :-)
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