Getting string value from SQL function inside a CLR

Question

Attempted (non-working) solution included below. I have an sql function called get_parameter which looks in a table for a given string and returns the associated string:

declare @str varchar(20);
set @str = dbo.get_parameter('filecount')
print @str

It works! I run this and it prints out exactly what it should print. In this case, the parameter is the string '44'.

Now I want to run a C# CLR. But I want the CLR to be able to look up the parameter that it needs.

    [SqlFunction(DataAccess = DataAccessKind.Read)]
    public static string Import_TestFunc()
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand command = new SqlCommand();
            command.Connection = conn;
            conn.Open();

            // Find out how many files DSAMS processing requires.
            command.CommandText = @"EXEC get_parameter 'filecount' ";
            string cnt = (string)command.ExecuteScalar();

            if (String.IsNullOrEmpty(cnt))
            {
                return "'cnt' not found."; // error return code. can't find this parameter.
            }
           return cnt;
        }
    }

However, this does not work. It constantly thinks the value for cnt is null (or empty) when it returns from get_parameter.

As requested, the code for get_parameter

ALTER FUNCTION [dbo].[get_parameter] 
(
    @SelectedParameterName nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @result nvarchar(max);

    SET @result = (SELECT ParameterValue from Parameters WHERE ParameterName = @SelectedParameterName);

    RETURN isnull(@result,'');

END

I have tried the solution as per Mike Dinescu below, but it problem is that the call to ExecuteScalar() still returns a null. I did try to change to CommandType.Text and in that case I get the following interesting message:

A .NET Framework error occurred during execution of user-defined routine or aggregate "Import_TestFunc": 
System.Data.SqlClient.SqlException: Procedure or function 'get_parameter' expects parameter '@SelectedParameterName', which was not supplied.

This is interesting, because I'm looking right at where it adds the parameter @SelectedParameterName.

  command.Parameters.Add(new SqlParameter("@SelectedParameterName", SqlDbType.NVarChar )).Value = "filecount";

Are you perhaps comparing apples with oranges? Your SQL function is not doing exactly the same as your manual script. `EXEC get_parameter 'filecount'` is not the same as `SET … = dbo.get_parameter('filecount')`. — stakx - no longer contributing, Sep 29 '14 at 18:24
You said this a function but in your CLR you are calling like a procedure. Change your CLR code to "Select dbo.get_parameter('filecount')" — Sean Lange, Sep 29 '14 at 18:24
Show us the definition of `dbo.get_parameter`. At least the header, if not the whole body. — RBarryYoung, Sep 29 '14 at 18:25

Mike Dinescu · Accepted Answer · 2014-09-30T14:54:21.587

1

If you want to execute a user-defined function, or stored procedure from .NET, you should set the CommandType to CommandType.StoredProcedure, and add the needed parameters to the command object before executing the command.

 command.CommandType = CommandType.StoredProcedure;
 command.CommandText = @"dbo.get_parameter";

 // here you add the paramters needed for your procedure/function
 //   in your case it will be just one (make sure you add the correct name for you function)
 command.Parameters.Add(new SqlParamter("SelectedParameterName", SqlDbType.NVarChar));

 command.Prepare();

 command.Parameters[0].Value = "filecount";

 string cnt = (string)command.ExecuteScalar();

edited Sep 30 '14 at 14:54

answered Sep 29 '14 at 18:24

Mike Dinescu

54,171
16
118
151

Doesn't recognize CommandType.StoredProcedure even though I have using System.Data.SqlClient, also doesn't recognize new SqlCommandParameter() – elbillaf Sep 29 '14 at 18:34
NVM. Adding using System.Data fixed both of those. – elbillaf Sep 29 '14 at 18:43
Interestingly, when I set CommandType.Text and set the CommandText to @"select parametervalue from IF103_Parameters where ParameterName = 'dsams_filecount'" ... IOW, when I use a direct select instead of a stored procedure call, it works. – elbillaf Sep 30 '14 at 16:48

Getting string value from SQL function inside a CLR

1 Answers1