MySQL分页存储过程

create PROCEDURE P_GetStudentList
(
_TrueName varchar(50),
_Age int,
_PageIndex int,
_PageSize int,
out _TotalCount int
)
BEGIN
       DECLARE _sql VARCHAR(200) DEFAULT 'select * from v_userlist ';-- 空格
       DECLARE _countSql VARCHAR(200) DEFAULT 'select count(ID) from v_userlist ';
       DECLARE _whereSql VARCHAR(200) DEFAULT ' where 1=1 ';
       if(_TrueName is not null and _TrueName<>'') THEN
            set _whereSql=CONCAT(_whereSql,' and TrueName like ''%',_TrueName,'%''');
       END IF;
       if(_Age>0) THEN
            set _whereSql=CONCAT(_whereSql,' and Age=',_Age);
       END if;

       -- 算总记录数
          set _countSql=CONCAT(_countSql,_whereSql,' into @totalCount');-- 将总记录数放在会话变量@totalCount
          set @_countSql=_countSql;
          PREPARE pre_count_sql from @_countSql;-- 预编译
          EXECUTE pre_count_sql;
          set _TotalCount=@totalCount;-- 保存总记录数
          DEALLOCATE PREPARE pre_count_sql;

            -- 拼接分页数据
         set _whereSql=CONCAT(_whereSql,' limit ',(_PageIndex-1)*_PageSize,',',_PageSize);
         set _sql=CONCAT(_sql,_whereSql);
         SET @_sql=_sql;
         PREPARE pre_sql from @_sql;
         EXECUTE pre_sql;
         DEALLOCATE PREPARE pre_sql;
END;