sql存储过程功能:向客户端返回表格信息(select ***)和消息(print),向变量返回值(return);
sql存储过程是由-数据定义语言和数据操作语言组成的一组SQL;
CLR存储过程:采用.net framework开发的MSSQL存储过程；
CLR存储过程:可完成sql存储过程所有功能,及调用系统API完成一些特殊功能。


例一: 创建带输出参数的clr存储过程

using System;  
using System.Data.SqlTypes;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server;   
  
public class StoredProcedures   
{  
   //求产品之和
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void ParOutTest(out SqlInt32 value)  
   {  
      using(SqlConnection connection = new SqlConnection("context connection=true"))   
      {  
         value = 0;  
         connection.Open();  
         SqlCommand command = new SqlCommand("SELECT qty FROM listInfo", connection);  
         SqlDataReader reader = command.ExecuteReader();  
  
         using (reader)  
         {  
            while( reader.Read() )  
            {  
               value += reader.GetSqlInt32(0);  
            }  
         }           
      }  
   }  
}  

---

注册程序集
CREATE ASSEMBLY [dll名称] FROM ‘C:\*****.dll’;

 

---

创建存储过程
CREATE PROCEDURE pr_testOutCanShu(@qtyAll int output)
AS EXTERNAL NAME [命名空间名称].StoredProcedures.ParOutTest;

 

---

执行存储过程
declare @t int
EXEC pr_testOutCanShu @t output;

 

---

例二: 创建返回消息的clr存储过程
clr存储过程中，通过SqlPipe.Send(String)想客户端发送文本消息

 

using System;  
using System.Data;  
using System.Data.SqlTypes;  
using System.Data.SqlClient;  
using Microsoft.SqlServer.Server; 
public class StoredProcedures   
{  
   /// 
  
   /// Execute a command and send the results to the client directly.  
   /// 
  
   [Microsoft.SqlServer.Server.SqlProcedure]  
   public static void PrintInfoToClient()  
   {  
   using(SqlConnection connection = new SqlConnection("context connection=true"))   
   {  
      connection.Open();  
      SqlCommand command = new SqlCommand("select @@version", connection);  
      SqlContext.Pipe.ExecuteAndSend(command);  
      }  
   }  
}

---

注册程序集
CREATE ASSEMBLY [dll名称] FROM ‘C:\*****.dll’;

 

---

创建存储过程
CREATE PROCEDURE pr_testPrintInfo()
AS EXTERNAL NAME [命名空间名称].StoredProcedures.PrintInfoToClient;

 

---

执行存储过程
EXEC pr_testPrintInfo;