How can I call a stored procedure within a SQL User Defined Function

Question

Hi,

I have created a User Defined function within a SQL Project. This C# Method needs to be able to call a Stored Procedure within the same database. How would I go about doing this?

Answer 1

your User defined function inside your SQL project is not in c# it is in SQL. If you have written it in c# then your question doesn't make sense.

If however you want to call a stored proc from c# then you need to look at ado.net the namespace is System.Data.SQLClient

Answer 2

With the assumption that your talking about a Managed / CLR user defined function, I don't think you can.

The only way that I can think of that you might be able to is if you create a new connection to the sql server from within your UDF - I can't be sure that this would work, but I am sure that this would be a bad idea - don't do it!

I have to ask - why would you want to do this anyway? CLR UDF's exist for the purpose of using small snippets of managed code (for example regular expressions) in your stored procedures, not processing large result sets (that's one thing that SQL is usually very good at)

Answer 3

If you need to call a SP in a compiled C# method inside the database, you have to ask yourself if something is wrong with your design. Managed code is not there for remplace Transact-SQL but for complementing the few thinkgs that it can't do by itself.

If you need to call a SP, then your code should be in another SP. Althought is dificult to decide if you don't say more details.

Answer 4

OK, so I've figured it out. What I needed to do is basically access a SQL Stored Procedure within a Managed CLR User Defined Function. And yes it might sound like a poor design issue, but within my application I need to do a quick lookup within my UDF, after a few days of thinking of a way around this (design) there's just no other way. So here is the code of how I done it, the UDF that gets called from within SQL needs to have the following attributes above the method signature: [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]

Within this UDF I then make another call to an internal method called 'GetLookup'. But because GetLookup requires access to the database, the calling method, the UDF in this case needs those attributes to sort out any access issues.

(Variable names etc. aren't the actual ones i've used)

private static int GetLookup(int id_Lookup)
{
    try
    {
        using (SqlConnection sqlConn = new SqlConnection("Context Connection=true"))
        {
            sqlConn.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "uspApp_GetLookup";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = sqlConn;
                cmd.Parameters.AddWithValue("ID_Lookup", ID_Lookup);
                SqlParameter parameter = new SqlParameter("OutputValue", null);
                parameter.DbType = DbType.Int32;
                parameter.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(parameter);

                cmd.ExecuteNonQuery();

                if (cmd.Parameters != null && cmd.Parameters["ID_OutputValue_Account"] != null)
                {
                    return int.Parse(cmd.Parameters["OutputValue"].Value.ToString());
                }
                return -1;
            }
        }
    }
    catch
    {
        throw;
    }
}