带OUTPUT参数的CLR存储过程

9/1/2015来源:SQL技巧人气:4971

带OUTPUT参数的CLR存储过程

前面写了一篇《带参数的CLR存储过程》http://www.cnblogs.com/insus/p/4373605.html ,如果我们需要创建一个带OUTPUT返回值。实现它,可以先了解一下C#的OUT关键词

out (C# Reference)

打开刚才前面我们写好的SQL DataBase PRoject,添加一个方法:

可复制代码:

 public static void GetFruitName(out SqlString fruitName, SqlByte fruit_nbr )    {        SqlConnection connection = new SqlConnection("Context connection=true");        connection.Open();        SqlCommand command = new SqlCommand();        command.Connection = connection;        string sql = "SELECT [FruitName] FROM [dbo].[Fruit] WHERE [Fruit_nbr] = @Fruit_nbr";        command.CommandText = sql;        SqlParameter param = new SqlParameter("@Fruit_nbr", SqlDbType.TinyInt);        param.Value = fruit_nbr;        command.Parameters.Add(param);        try        {            string scalarValue = (string)command.ExecuteScalar();            fruitName = (SqlString)scalarValue;        }        catch         {            fruitName = string.Empty;        }        connection.Close();    }
View Code

Build solution,然后找开Microsoft SQL Server management studio,重新部署Assembly以及Clr stored procedure。参考前两篇《简单创建与布署CLR存储过程》http://www.cnblogs.com/insus/p/4371762.html 和 《带参数的CLR存储过程》http://www.cnblogs.com/insus/p/4373605.html

执行存储过程: