DELIMITER $$USE `iespdb`$$DROP PROCEDURE IF EXISTS `RAS_Sys_Pageination`$$CREATE DEFINER=`root`@`%` PROCEDURE `RAS_Sys_Pageination`(
IN `i_sql` VARCHAR(4000), -- 不带排序语句的SQL语句
IN `i_page` INT,-- 页码
IN `i_recsPerPage` INT,-- 每页容纳的记录数
IN `i_id` VARCHAR(255),-- 需要排序的不重复的ID号
IN `i_sort` VARCHAR(255),-- 排序字段及规则
INOUT `o_total` INT)-- 返回待查询记录的总数
BEGIN
DECLARE v_sql VARCHAR(4000);SET @aa='select '+o_total+'=count(*) into o_total from '+i_sql+'';
PREPARE st1 FROM @aa;
EXECUTE st1;
IF(i_sort IS NULL OR i_sort = '') THEN
BEGIN
SET i_sort = i_id;
END;
END IF;
IF (i_page=1) THEN
BEGIN
SET @SQL='select * from '+i_sql+' order by '+i_sort+' limit '+i_recsPerPage+''; PREPARE st2 FROM @SQL;
EXECUTE st2;
END;
ELSE
BEGIN
SET @SQL='select * from '+i_sql+' t1 where t1.'+i_id+' not in (select t2.'+i_id+' from '+i_sq+' as t2 order by '+i_sort+' limit '+i_recsPerPage+' * ('+i_page+'-1)) order by '+i_sort+' limit '+i_recsPerPage+'';
PREPARE st FROM @SQL;
EXECUTE st;
END;
END IF;
END$$DELIMITER ;执行 CALL RAS_Sys_Pageination('select *from EOW_INB_Record','1','12','appId','appId desc',@a);
SELECT @a;报You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1
请问各位大牛 这怎么回事啊?本人刚入门,还望不吝赐教,谢谢
IN `i_sql` VARCHAR(4000), -- 不带排序语句的SQL语句
IN `i_page` INT,-- 页码
IN `i_recsPerPage` INT,-- 每页容纳的记录数
IN `i_id` VARCHAR(255),-- 需要排序的不重复的ID号
IN `i_sort` VARCHAR(255),-- 排序字段及规则
INOUT `o_total` INT)-- 返回待查询记录的总数
BEGIN
DECLARE v_sql VARCHAR(4000);SET @aa='select '+o_total+'=count(*) into o_total from '+i_sql+'';
PREPARE st1 FROM @aa;
EXECUTE st1;
IF(i_sort IS NULL OR i_sort = '') THEN
BEGIN
SET i_sort = i_id;
END;
END IF;
IF (i_page=1) THEN
BEGIN
SET @SQL='select * from '+i_sql+' order by '+i_sort+' limit '+i_recsPerPage+''; PREPARE st2 FROM @SQL;
EXECUTE st2;
END;
ELSE
BEGIN
SET @SQL='select * from '+i_sql+' t1 where t1.'+i_id+' not in (select t2.'+i_id+' from '+i_sq+' as t2 order by '+i_sort+' limit '+i_recsPerPage+' * ('+i_page+'-1)) order by '+i_sort+' limit '+i_recsPerPage+'';
PREPARE st FROM @SQL;
EXECUTE st;
END;
END IF;
END$$DELIMITER ;执行 CALL RAS_Sys_Pageination('select *from EOW_INB_Record','1','12','appId','appId desc',@a);
SELECT @a;报You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1
请问各位大牛 这怎么回事啊?本人刚入门,还望不吝赐教,谢谢
SET @aa=concat('select count(*) into o_total from (',i_sql, ') a');显示一下@AA中的内容即可
SET @aa=concat('select count(*) into o_total from (',i_sql, ') a');显示一下@AA中的内容即可
-------------------------------------------------------------
不行啊 还是报错 看一下我调用存储过程的 方式有错么
SELECT @AA,结果是什么
改成
SET @aa=concat('select count(*) into @o_total from (',i_sql, ') a');
set o_total = @o_total
就好了
执行 CALL RAS_Sys_Pageination('select *from EOW_INB_Record','1','12','appId','appId desc',@a);
SELECT @a;
就能得到结果了
String mssql = "{call RAS_Sys_Pageination(?,?,?,?,?,?)}";
this.jdbcTemplate.execute(mssql, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
cs.setString(1, sql);
cs.setInt(2, curPage);
cs.setInt(3, perPage);
cs.setString(4, idField);
cs.setString(5, sort);
cs.setString(6, );
ResultSet rs = cs.executeQuery();