mysql 返回查询结果，返回out返回值，多表联合查询的分页存储过程

先谈谈又想返回out返回值，又想返回sql查询结果记录集的问题，这个在mssql里面是OK的，但是在mysql里面是不行的，原因请看

 

那么解决方法是什么呢？就是Mysql支持多个结果集的返回

用 read 先读取第一个记录集，然后再用 nextResult来读取第二个即可

具体请看http://www.cnblogs.com/joeylee/archive/2013/02/02/2889806.html

 

例如sql语句是
SELECT me.*, GROUP_CONCAT(re.RId) AS rids FROM tb_sc_smessage me INNER JOIN tb_sc_smrecive re
ON me.MId=re.MId
WHERE me.TId='6d73f2fd-92fb-4f5c-a00a-e5f926543a0f'  AND me.FunctionId=5

AND SendTime>=p_BeginTime AND SendTime<=p_EndTime
GROUP BY re.MId
ORDER BY sendtime DESC;

那么我们修改成存储过程为

DELIMITER $$

USE `date`$$

DROP PROCEDURE IF EXISTS `XiaoWuHistory`$$

CREATE DEFINER=`root`@`%` PROCEDURE `XiaoWuHistory`(p_Tid VARCHAR(64),p_Functionid INT,p_BeginTime VARCHAR(64),p_EndTime VARCHAR(64),IN p_pageIndex INT,             
	IN p_pageSize INT )
BEGIN
	--   定义key字段临时表 
	DROP   TABLE   IF   EXISTS   _temptable_keyid;     --   删除临时表,如果存在 
	CREATE   TEMPORARY     TABLE     _temptable_keyid 
	( 
		`mid` INT,
		`content` VARCHAR(2048),
		functionid INT,
		tid VARCHAR(64),
		tname VARCHAR(64),
		sendtime DATETIME,
		rids VARCHAR(50000)		
	);
	--   构建动态的sql，输出关键字key的id集合 
	--   查找条件 
	SET   @SQL   ='SELECT me.*, GROUP_CONCAT(re.RId) AS rids FROM smessage me INNER JOIN smrecive re ON me.MId=re.MId'; 
	SET 	@SQL=CONCAT(@SQL, '  where me.TId=''',p_Tid,''' '); 
	
	IF (p_BeginTime!=''  OR p_BeginTime!= NULL)THEN
		SET   @SQL=CONCAT(@SQL, '  and SendTime>=''',p_BeginTime,'''  '); 
		SET   @SQL=CONCAT(@SQL, '  and  SendTime<=''',p_EndTime,'''  '); 
	END IF;	
	
	SET   @SQL=CONCAT(@SQL, ' and me.FunctionId=''',p_Functionid,''' GROUP BY re.MId ORDER BY sendtime DESC '); 

--   准备id记录插入到临时表 
	SET   @SQL=CONCAT('insert   into   _temptable_keyid(`mid`,`content`,`functionid`,`tid`,`tname`,`sendtime`,`rids`) ', @SQL); 
	PREPARE   stmt   FROM   @SQL; 
	EXECUTE   stmt   ; 
	DEALLOCATE   PREPARE   stmt; 
	--   下面是输出 
	SELECT   COUNT(*) AS  RecordCount FROM _temptable_keyid; 
	
	--   计算记录的起点位置 
	SET   @STARTPOINT   =   IFNULL((p_pageIndex-1)*p_pageSize,0); 
	
	SET   @SQL='select * from _temptable_keyid  ';
	
	SET   @SQL=CONCAT(@SQL, ' limit ',@STARTPOINT,' , ',p_pageSize); 
	PREPARE   stmt   FROM   @SQL; 
	EXECUTE   stmt   ; 
	DEALLOCATE   PREPARE   stmt; 
	DROP   TABLE   _temptable_keyid; 


END$$

DELIMITER ;

在mysql里面调用试试

CALL fn_TWeb_XiaoWuHistory('6d73f2fd-92fb-4f5c-a00a-e5f926543a0f',5,'1900-01-01','2013-03-25 10:49:06',1,3)

结果如下

 

这样就返回了2个结果集，一个表示有多少条数据，一个就是通过分页查询到的结果,那么前台如何调用呢？

 

1:  repContent.DataSource = jkController.GetXiaoWuHistoryByPage(Convert.ToInt32(ddlType.SelectedValue), User.Identity.Name, null, null, pg, pagesize, ref recordcount);

2: repContent.DataBind();

3:   

 

这里就是获取2个结果集，用 read 先读取第一个记录集，然后再用 nextResult来读取第二个即可

 1:  public List<MGetXiaoWuHistory> GetXiaoWuHistoryByPage(int functionId, string tId, string beginTime, string endTime, int pg, int pagesize, ref int recordcount)

 2:          {

 3:              IDataReader rdr = DataProvider.Instance().GetXiaoWuHistoryByPage(functionId, tId, beginTime, endTime, pg, pagesize);

 4:              List<MGetXiaoWuHistory> objArray = null;

 5:              if (rdr.Read())

 6:              {

 7:                  recordcount = Convert.ToInt32(rdr["RecordCount"]);

 8:              }

 9:   

10:              if (rdr.NextResult())

11:              {

12:                  objArray = CBO.FillCollection<MGetXiaoWuHistory>(rdr);

13:              }

14:              return objArray;

15:   

16:          }

17:   

最后是存储过程

 1:  public override IDataReader GetXiaoWuHistoryByPage(int functionId, string tId, string beginTime, string endTime, int pg, int pagesize)

 2:          {

 3:              MySqlParameter[] parameters = {

 4:                      new MySqlParameter("@p_Tid", MySqlDbType.VarChar,64),

 5:                      new MySqlParameter("@p_Functionid", MySqlDbType.Int32),

 6:                      new MySqlParameter("@p_BeginTime", MySqlDbType.VarChar,64),

 7:                      new MySqlParameter("@p_EndTime", MySqlDbType.VarChar,64),

 8:                      new MySqlParameter("@p_pageIndex", MySqlDbType.Int32),

 9:                      new MySqlParameter("@p_pageSize", MySqlDbType.Int32)

10:                                            };

11:              parameters[0].Value = tId;

12:              parameters[1].Value = functionId;

13:              parameters[2].Value = beginTime;

14:              parameters[3].Value = endTime;

15:              parameters[4].Value = pg;

16:              parameters[5].Value = pagesize;

17:   

18:              MySqlConnection sqlConn = MySqlHelper.OpenConnection(FenXiaoConnStr);

19:              return MySqlHelper.ExecuteReader(sqlConn, CommandType.StoredProcedure, "fn_TWeb_XiaoWuHistory", parameters);

20:          }

21: