一个关于mysql分页查询的存储过程写法

DELIMITER $$
DROP PROCEDURE IF EXISTS `drugConsumptionStatistics`$$
CREATE PROCEDURE drugConsumptionStatistics(
  -- 输入参数
	IN hosCode VARCHAR(128),-- 机构编码
	IN prescriptionSource VARCHAR(128),-- 处方来源
	IN deptCode VARCHAR(128),-- 科室编码
	IN startTime VARCHAR(128),-- 开始时间
	IN endTime VARCHAR(128),-- 结束时间
	IN curPage INT, -- 当前页
	IN pageSize INT, -- 每页的数据量
	
	#输出参数
	OUT totalCount INT,  #总记录数
	OUT pageCount INT    #总页数
)
BEGIN
	-- 计算起始行号
	SET @startRow = pageSize * (curPage - 1);
	SET @pageSize = pageSize;
	SET @rowindex = 0;
	
	SET @whereStr = '';
	-- 机构编码
	IF (hosCode<>'') THEN
	   SET @whereStr = CONCAT(@whereStr, ' and tp.HosCode = ', hosCode);
  END IF;
	
	-- 处方来源
	IF (prescriptionSource<>'') THEN
	   SET @whereStr = CONCAT(@whereStr, ' and tp.PrescriptionSource = ', prescriptionSource);
  END IF;
	
	-- 科室
	IF (deptCode<>'') THEN
	   SET @whereStr = CONCAT(@whereStr, ' and tp.DeptCode = ', deptCode);
  END IF;
	
	-- 开始时间
	IF (startTime<>'') THEN
	   SET @whereStr = CONCAT(@whereStr, ' and tp.PrescriptionTime >= ', startTime);
  END IF;
	
	-- 结束时间
	IF (endTime<>'') THEN
	   SET @whereStr = CONCAT(@whereStr, ' and tp.PrescriptionTime <= ', endTime);
  END IF;
	
	SET @whereStr = CONCAT(@whereStr, ' group by tp.HosCode, tp.PrescriptionSource , tp.DeptCode ');


	-- 抗菌药物相关数据统计
	SET @AntiBacter = CONCAT('SELECT 
	tp.HosCode as hosCode, 
	tp.PrescriptionSource as prescriptionSource, 
	tp.DeptCode as deptCode, 
	count(DISTINCT(tp.JZTClaimNo)) as preOfAntiBacter, 
	count(DISTINCT(tp.ClientCardCode)) as patOfAntiBacter 
  FROM t_prescription_drugs tpd 
  left join t_prescription tp 
  on tpd.JZTClaimNo = tp.JZTClaimNo 
	where tpd.IsAntiBacter = 1 ', @whereStr);

	-- 基药相关数据统计
	SET @Basic = CONCAT('SELECT tp.HosCode as hosCode, tp.PrescriptionSource as prescriptionSource, tp.DeptCode as deptCode, count(DISTINCT(tp.JZTClaimNo)) as preOfBasic, count(DISTINCT(tp.ClientCardCode)) as patOfBasic 
FROM t_prescription_drugs tpd left join t_prescription tp on tpd.JZTClaimNo = tp.JZTClaimNo where tpd.IsBasic = 1 ', @whereStr);

	-- 注射药相关数据统计
	SET @Injection = CONCAT('SELECT 
	tp.HosCode as hosCode, 
	tp.PrescriptionSource as prescriptionSource, 
	tp.DeptCode as deptCode, 
	count(DISTINCT(tp.JZTClaimNo)) as preOfInjection, 
	count(DISTINCT(tp.ClientCardCode)) as patOfInjection 
  FROM t_prescription_drugs tpd 
  left join t_prescription tp 
	on tpd.JZTClaimNo = tp.JZTClaimNo 
	where tpd.IsInjection = 1 ', @whereStr);

	-- 处方信息
	SET @Prescription = CONCAT('SELECT 
	tp.HosCode as hosCode, 
	tp.PrescriptionSource as prescriptionSource, 
	tp.DeptCode as deptCode, 
	tp.HosName as hosName, 
	tp.DeptName as deptName, 
	count(DISTINCT(tp.jZTClaimNo)) as totalOfPrescription,
  count(DISTINCT(tp.ClientCardCode)) as totalOfPatient,             convert(count(DISTINCT(tpd.DrugCode))/count(tp.jZTClaimNo),decimal(10,2)) as aveOfPrescriptionDrugs, convert(count(DISTINCT(tpd.DrugCode))/count(DISTINCT(tp.ClientCardCode)),decimal(10,2)) as aveOfPatientDrugs 
	from t_prescription_drugs tpd 
	left join t_prescription tp 
	on tpd.JZTClaimNo = tp.JZTClaimNo 
	where 1 = 1 ', @whereStr);
	
	SET @strSql = CONCAT(
	#'select sql_calc_found_rows  @rowindex:=@rowindex+1 as rownumber,' #记录行号
	'select sql_calc_found_rows ',
	' x.*, 
	  IFNULL(y.preOfAntiBacter, 0) as preOfAntiBacter, 
		IFNULL(convert((y.preOfAntiBacter/x.totalOfPrescription),decimal(10,2)), 0) as preProportionsOfAnt, 
		IFNULL(y.patOfAntiBacter, 0) as patOfAntiBacter, 
		IFNULL(convert((y.patOfAntiBacter/x.totalOfPatient),decimal(10,2)), 0) as patProportionsOfAnt, 
		IFNULL(z.preOfBasic, 0) as preOfBasic, 
		IFNULL(convert((z.preOfBasic/x.totalOfPrescription),decimal(10,2)), 0) as preProportionsOfBasic, 
		IFNULL(z.patOfBasic, 0) as patOfBasic, 
		IFNULL(convert((z.patOfBasic/x.totalOfPatient),decimal(10,2)), 0) as patProportionsOfBasic,
		IFNULL(w.preOfInjection, 0) as preOfInjection, 
		IFNULL(convert((w.preOfInjection/x.totalOfPrescription),decimal(10,2)), 0) as preProportionsOfInjection, 
		IFNULL(w.patOfInjection, 0) as patOfInjection, 
		IFNULL(convert((w.patOfInjection/x.totalOfPatient),decimal(10,2)), 0) as patProportionsOfInjection
		FROM (', @Prescription, ') x 
		left join (', @AntiBacter, ') y 
		on x.HosCode = y.HosCode and x.PrescriptionSource = y.PrescriptionSource and x.DeptCode = y.DeptCode 
		left join (', @Basic, ') z 
		on x.HosCode = z.HosCode and x.PrescriptionSource = z.PrescriptionSource and x.DeptCode = z.DeptCode 
		left join (', @Injection, ') w 
		on x.HosCode = w.HosCode and x.PrescriptionSource = w.PrescriptionSource and x.DeptCode = w.DeptCode limit '
		,@startRow
		,',' 
		,@pageSize
		);
	
  PREPARE strSql FROM @strSql;#定义预处理语句 
	EXECUTE strSql;							#执行预处理语句 
	DEALLOCATE PREPARE strSql;	#删除定义 
	#通过 sql_calc_found_rows 记录没有使用 limit 语句的记录，使用 found_rows() 获取行数
	SET totalCount = FOUND_ROWS();
	
	#计算总页数
	IF (totalCount <= pageSize) THEN
		SET pageCount = 1;
	ELSE IF (totalCount % pageSize > 0) THEN
		SET pageCount = totalCount / pageSize + 1;
	ELSE
		SET pageCount = totalCount / pageSize;
	END IF;
	END IF;

END;

-- 测试
CALL drugConsumptionStatistics(
''
,''
,''
,''
,''
,1 
,10 
,@totalcount #输出总记录数
,@pagecount #输出用页数
);
SELECT @totalcount,@pagecount;