using Microsoft.SqlServer.Server;
namespace SendYouResultSet
{
public class SendYouResultSetClass
{
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void SendYouResultSet(
decimal LowerSalary, decimal HigherSalary)
{
SqlCommand command;
// 使用内容连接来进行连接。
using (SqlConnection connection =
new SqlConnection("context connection=true"))
{
connection.Open();
command = new SqlCommand(
"SELECT 员工编号,姓名,目前薪资 FROM dbo.章立民研究室 " +
"WHERE 目前薪资 BETWEEN @LowerSalary AND @HigherSalary",
connection);
command.Parameters.AddWithValue("@LowerSalary", LowerSalary);
command.Parameters.AddWithValue("@HigherSalary", HigherSalary);
// 调用 ExecuteAndSend 方法执行 SqlCommand
// 并查询结果直接传送给用户端
SqlContext.Pipe.ExecuteAndSend(command);
}
}
}
}
请大家注意,此CLR 存储过程的两个参数都是输入参数,因此使用传值方式来传递。另外请特别注意,CLR 存储过程是使用 “内容连接” 来连接至 SQL Server(亦即程序中的 SqlConnection("context connection=true"))。
USE 北风贸易;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'SendYouResultSet')
DROP PROCEDURE SendYouResultSet;
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'SendYouResultSet')
DROP ASSEMBLY SendYouResultSet;
GO
-- 登录组件SendYouResultSet.dll
CREATE ASSEMBLY SendYouResultSet
FROM
'C:\SQL2005Demo\CH13\SendYouResultSet\SendYouResultSet\bin\SendYouResultSet.dll'
WITH permission_set = Safe;
GO
-- 建立一个会引用所登录之组件SendYouResultSet.dll 的存储过程
CREATE PROCEDURE dbo.SendYouResultSet
(
@LowerSalary money, @HigherSalary money
)
AS EXTERNAL NAME
SendYouResultSet.[SendYouResultSet.SendYouResultSetClass].SendYouResultSet;
GO
EXEC sp_configure 'clr enabled', '1';
GO
RECONFIGURE;
GO
DECLARE @return_value int
-- 执行CLR 存储过程
EXECUTE @return_value = dbo.SendYouResultSet
@LowerSalary = 50000,
@HigherSalary = 70000;