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;