Parameter 'recordcount' not found in the collection.
写了一个分页的存储过程,如下
DROP PROCEDURE IF EXISTS `PageSection`$$
CREATE DEFINER=`root`@`%` PROCEDURE `PageSection`(IN p_table_name VARCHAR(4000),IN p_fields VARCHAR(1024),IN p_pageNo INT,IN p_perPageCnt INT,IN p_order_string VARCHAR(128),IN p_where_string VARCHAR(4000),OUT recordcount LONG)
BEGIN
SET @pageCnt = 1;
SET @limitStart = (p_pageNo - 1)*p_perPageCnt;
SET @limitEnd = p_perPageCnt;
SET @sqlCnt=CONCAT('select count(*) into @pageCnt from ',p_table_name);
IF(p_fields='' OR p_fields IS NULL)THEN
SET @ssql = CONCAT('select * from ', p_table_name);
ELSE
SET @ssql = CONCAT('select',p_fields,' from ',p_table_name);
END IF;
IF(p_where_string IS NOT NULL AND p_where_string<>'')THEN
SET @ssql = CONCAT(@ssql,' where ',p_where_string);
SET @sqlCnt = CONCAT(@sqlCnt,' where ',p_where_string);
END IF;
IF(p_order_string IS NOT NULL AND p_order_string<> '')THEN
SET @ssql = CONCAT(@ssql,' order by ',p_order_string);
END IF;
SET @ssql= CONCAT(@ssql, ' LIMIT ', @limitStart, ',', @limitEnd);
PREPARE s_cnt FROM @sqlCnt;
EXECUTE s_cnt;
DEALLOCATE PREPARE s_cnt;
SET recordcount=@pageCnt;
PREPARE record FROM @ssql;
EXECUTE record;
DEALLOCATE PREPARE record;
END$$
DELIMITER ;
在C#中调用该存储过程如下:
#region 存储过程参数设置
MySqlParameter tablenameparm = new MySqlParameter("p_table_name", MySqlDbType.VarChar);
tablenameparm.Direction = ParameterDirection.Input;
tablenameparm.Value = tableName;
MySqlParameter fildsparm = new MySqlParameter("p_fields", MySqlDbType.VarChar);
fildsparm.Direction = ParameterDirection.Input;
fildsparm.Value = filds;
MySqlParameter wherefield = new MySqlParameter("p_where_string", MySqlDbType.VarChar);
wherefield.Direction = ParameterDirection.Input;
wherefield.Value = strWhere;
MySqlParameter pageindexfield = new MySqlParameter("p_pageNo", MySqlDbType.Int32);
pageindexfield.Direction = ParameterDirection.Input;
pageindexfield.Value = pageIndex;
MySqlParameter perpageCnt = new MySqlParameter("p_perPageCnt", MySqlDbType.Int32);
perpageCnt.Direction = ParameterDirection.Input;
perpageCnt.Value = pageCount;
MySqlParameter porderparm = new MySqlParameter("p_order_string", MySqlDbType.VarChar);
porderparm.Direction = ParameterDirection.Input;
porderparm.Value = orderValue;
MySqlParameter recordtotalfield = new MySqlParameter("recordcount", MySqlDbType.Int64);
recordtotalfield.Direction = ParameterDirection.Output;
#endregion
MySqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.connString, CommandType.StoredProcedure, "PageSection", tablenameparm, fildsparm, wherefield, pageindexfield, perpageCnt, porderparm, recordtotalfield);
int result = SqlHelper.ExecuteNonQuery(SqlHelper.connString, CommandType.StoredProcedure, "PageSection", tablenameparm, fildsparm, wherefield, pageindexfield, perpageCnt, porderparm, recordtotalfield);
但是总抛出异常,如题所示:
Parameter 'recordcount' not found in the collection.
这个问题到底该怎么解决呀。查了好几天,解决方案中都说是名称错误和不匹配,但是我这儿没错。自己尝试调用过这个
存储过程,也没有什么问题。求达人帮助!