从sql存储过程多个结果集中获取指定的单个结果集
很久不写代码了。日前有个脑水的朋友提出要从sql存储过程多个结果集中取得一个想要的结果集在自己的另一个存储过程中用，我考！
我回答：“自己写个去用@Table,游标，临时表都可以。”
他又说：“那我不是要重写很多？”
接着又说：“我要从系统存储过程中得到，那我还要重写系统存储过程！”
自己回去看了一下，也上网查了查，确实没有啥好偷懒的办法。真的就没有吗？不是还有CLR存储过程吗？对说干就干，写一个！
先给自己定一个目标，这个存储过程必须调用已存在的存储过程；这个存储过程必须能指定返回某个想要的结果集；这个存储过程有比较广的适用性。
看起来应该是这样的：
exec CLRSP_PROCEDURE @command1="要调用的存储过程"， @command2="调用的存储过程的参数" @command3=要获取的结果集的索引
来吧现在看代码
1 using System; 2 using System.Collections; 3 using System.Data; 4 using System.Data.SqlClient; 5 using System.Data.SqlTypes; 6 using Microsoft.SqlServer.Server; 7 8 public partial class StoredProcedures 9 { 10 /// <summary> 11 /// 获取存储过程返回多个结果集中的一个 12 /// </summary> 13 /// <param name="Sql">存储过程名称</param> 14 /// <param name="Params">执行存储过程所要提供的参数名称及参数值.多个条段以逗号分割.</param> 15 /// <param name="returnidex">选择要返回的存储过程返回多个结果集中的索引.以0开始.</param> 16 [Microsoft.SqlServer.Server.SqlProcedure] 17 public static void ZDFHJGJ(SqlString Sql, SqlString Params, SqlInt32 returnidex) 18 { 19 //检查存储过程名称是否赋值 20 if (Sql == SqlString.Null) 21 return; 22 23 //创建连接 24 using (SqlConnection con = new SqlConnection("context connection=true")) 25 { 26 //声明并初始化一个SqlCommand 27 SqlCommand cmmd = new SqlCommand(); 28 cmmd.CommandType =CommandType.StoredProcedure; 29 cmmd.CommandText = Sql.Value; 30 cmmd.Connection = con; 31 //拆分传入的存储过程参数，并附给SqlCommand的Parameters 32 if (!Params.IsNull) 33 { 34 ArrayList ar=new ArrayList( ((string)Params).Split(new Char[]{','},StringSplitOptions.RemoveEmptyEntries )); 35 foreach (string s in ar) 36 { 37 string[] ss = s.Split(new Char[] { '=' }, StringSplitOptions.RemoveEmptyEntries); 38 SqlParameter sq = new SqlParameter(ss[0],ss[1]); 39 cmmd.Parameters.Add(sq); 40 } 41 } 42 //打开连接 43 con.Open(); 44 try 45 { 46 //有一种选择是返回所有 47 if (returnidex.Value == 9999 || returnidex == SqlInt32.Null) 48 { 49 SqlContext.Pipe.ExecuteAndSend(cmmd); 50 } 51 else 52 { 53 //返回指定的结果集 54 //设置一个找到指定结果集的标识 55 Boolean flag = false; 56 //执行，获取所有结果集 57 SqlDataReader reader = cmmd.ExecuteReader(); 58 //设置一个计数，来统计和标识当前结果集 59 int i = 0; 60 //开始读取结果集 61 do 62 { 63 64 //检查是否是指定要返回的结果集 65 if (i == returnidex) 66 { 67 /* 68 * 当前结果集是要返回的结果集 69 * 由于sql server CLR的限制，不能使用DataTable、DataReader返回存储过程结果集 70 * 所以只能选择SqlContext.Pipe.SendResultsRow来返回结果集 71 * 构建一个列集 72 * 声明一个SqlMetaData[]数组，SqlDataReader.VisibleFieldCount属性.SqlDataReader.FieldCount属性包括一些不可见字段. 73 */ 74 SqlMetaData[] sqlmd = new SqlMetaData[reader.VisibleFieldCount]; 75 //构建列集实例 76 for (int ii = 0; ii < reader.VisibleFieldCount; ii++) 77 { 78 // 因为SqlTypes与SqlDbType存在一对多关系.如果没有明确要转换的类型会出现想不到的问题,所以SqlDbType.Variant类型就成为唯一选择. 79 sqlmd[ii] = new SqlMetaData(reader.GetName(ii), SqlDbType.Variant); 80 } 81 //构建一个SqlDataRecord行容器实例 82 //将列集加入到行容器 83 SqlDataRecord sqlrd = new SqlDataRecord(sqlmd); 84 //使用 SqlContext.Pipe.SendResultsStart(行容器变量)和SqlContext.Pipe.SendResultsEnd()的方法对发送结果集 85 SqlContext.Pipe.SendResultsStart(sqlrd); 86 //用SqlDataReader.Read()填充SqlMetaData 87 while (reader.Read()) 88 { 89 for (int j = 0; j < reader.VisibleFieldCount; j++) 90 { 91 sqlrd.SetValue(j, reader.GetValue(j)); 92 } 93 //填充完毕，发送结果行 94 SqlContext.Pipe.SendResultsRow(sqlrd); 95 }; 96 //所有结果行发送完毕 97 SqlContext.Pipe.SendResultsEnd(); 98 //结果集已找到并返回，将找到指定结果集的标识设为True 99 flag = true; 100 //跳出循环 101 break; 102 } 103 //不是指定要返回的结果集，计数加1，准备处理下个结果集 104 i++; 105 106 } while (reader.NextResult());//移动到下一结果集 107 //判断是否找到并以返回结果集。如果否则发送消息给调用者 108 if (!flag) 109 SqlContext.Pipe.Send("不存在所要返回的索引为" + returnidex.ToString() + "的结果集"); 110 111 } 112 } 113 catch(SqlException se) 114 { 115 //如果出现错误，将错误的消息发送给调用者 116 SqlContext.Pipe.Send(se.Message); 117 } 118 finally 119 { 120 //保持良好习惯，用完关闭连接 121 if (con.State != ConnectionState.Closed) 122 con.Close(); 123 } 124 } 125 } 126 127 128 129 }
好了，现在把它安装的sql server里，来运行一下吧！
EXEC [dbo].[CLRSP_GetResult] "sys.sp_helplogins", --调用系统存储过程 "@LoginNamePattern=xx", --只查一个登录名 1 --返回第二个结果集
完成！
