先亮出程序提示错误!:OUT or INOUT argument 9 for routine sosnet.sp_ProPageResult is not a variable or NEW pseudo-variable in BEFORE trigger
在网上找的mysql分页存储过程
DELIMITER $$USE `sosnet`$$DROP PROCEDURE IF EXISTS `sp_ProPageResult`$$CREATE PROCEDURE `sp_ProPageResult`(IN tablename VARCHAR(1024), /*表名*/
IN tablecolumns VARCHAR(1024), /*查询字段*/
IN pagesize INT, /*每页记录数*/
IN currpage INT, /*当前页*/
IN order_field VARCHAR(100), -- 要排序的字段
IN asc_field INT, -- 排序值(1表示desc,0表示asc)
IN sCondition LONGTEXT,-- where条件
IN sGroupBy LONGTEXT,-- GROUP BY
OUT rowstotal INT)-- 返回记录数
COMMENT '分页存储过程'
BEGIN
DECLARE p_order_string VARCHAR(1000);
DECLARE p_where_string VARCHAR(1024);
DECLARE CURSQLCOUNT VARCHAR(3000);
/*定义变量*/
DECLARE m_begin_row INT DEFAULT 0;
DECLARE m_limit_string CHAR(64);
/*定义排序*/
IF asc_field = 1 THEN
SET p_order_string = CONCAT(' order by ', order_field, ' desc ');
ELSE
SET p_order_string = CONCAT(' order by ', order_field, ' asc ');
END IF;
/*定义where*/
IF sCondition IS NOT NULL AND sCondition <>'' THEN
SET p_where_string = CONCAT(' where ', sCondition);
ELSE
SET p_where_string = '';
END IF;
/*记录统计*/
SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', tablename, p_where_string);
IF (sGroupBy<>'') THEN
SET p_where_string=CONCAT(p_where_string,' ',sGroupBy);
END IF;
/*构造语句*/
SET m_begin_row = (currpage - 1) * pagesize;
SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', pagesize);
SET @MAIN_STRING = CONCAT('SELECT ', tablecolumns, ' FROM ', tablename, ' ', p_where_string, ' ', p_order_string, m_limit_string);
/*预处理*/
-- select @COUNT_STRING;
PREPARE CURSQLCOUNT FROM @COUNT_STRING;
EXECUTE CURSQLCOUNT;
SET rowstotal = @ROWS_TOTAL;
PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
END$$DELIMITER ;下面是ASP.NET调用存储过程:
public static DataTable SqlGetDataTable(string proc, CommandType type, string[] paramValue, out int OutTotalCount)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
DataSet ds = new DataSet();
using (MySqlCommand cmd = new MySqlCommand(proc, connection))
{
try
{
//分页开始
MySqlParameter[] myParms = new MySqlParameter[9];
myParms[0] = new MySqlParameter("@tablename", MySqlDbType.VarChar, 1024);
myParms[0].Value = paramValue[0];
myParms[1] = new MySqlParameter("@tablecolumns", MySqlDbType.VarChar, 1024);
myParms[1].Value = paramValue[1];
myParms[2] = new MySqlParameter("@pagesize", MySqlDbType.Int32, 5);
myParms[2].Value = paramValue[2];
myParms[3] = new MySqlParameter("@currpage", MySqlDbType.Int32, 5);
myParms[3].Value = paramValue[3];
myParms[4] = new MySqlParameter("@order_field", MySqlDbType.VarChar, 500);
myParms[4].Value = paramValue[4];
myParms[5] = new MySqlParameter("@asc_field", MySqlDbType.VarChar, 500);
myParms[5].Value = paramValue[5];
myParms[6] = new MySqlParameter("@sCondition", MySqlDbType.VarChar, 500);
myParms[6].Value = paramValue[6];
myParms[7] = new MySqlParameter("@sGroupBy", MySqlDbType.VarChar, 500);
myParms[7].Value = paramValue[7];
myParms[8] = new MySqlParameter("@rowstotal", MySqlDbType.Int32, 5);
myParms[8].Value = ParameterDirection.Output;
foreach (MySqlParameter parameter in myParms)
{
cmd.Parameters.Add(parameter);
}
cmd.CommandType = type;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(ds);//这里出错:OUT or INOUT argument 9 for routine sosnet.sp_ProPageResult is not a variable or NEW pseudo-variable in BEFORE trigger
//out 总记录数
OutTotalCount = Convert.ToInt32(myParms[8].Value);
return ds.Tables[0];
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
}下面是Asp.NET的方法调用public static DataTable getPageList(string tableName, string sqlColumns, int size, int index, string orderColumns, int orderTag, string where, string sGroupBy, out int OutTotalCount)
{
DataTable dt = null;
//分页参数
string[] paramValue =
{
tableName,//表名
sqlColumns,//查询字段
""+size,//每页显示条数
""+index,//当前页
orderColumns,//要排序的字段
""+orderTag,//排序值(1表示desc,0表示asc)
where,//where条件
sGroupBy//group by (使用时需包含 group by)
};
dt = DbHelperMySQL.SqlGetDataTable("sp_ProPageResult", CommandType.StoredProcedure, paramValue, out OutTotalCount);
return dt;
}
int count;
DataTable dt = Maticsoft.Common.PagerList.getPageList("s_area", "*", 10, 1, "a_id", 1, "", "", out count);
PS:请问.net中调用mysql存储过程时 到底是用@还是? 有什么区别!!!虽然Mysql小 但个人很不喜欢!!!!!!!!感觉比MSSQL差远了MySQL存储ASP.NET分页
在网上找的mysql分页存储过程
DELIMITER $$USE `sosnet`$$DROP PROCEDURE IF EXISTS `sp_ProPageResult`$$CREATE PROCEDURE `sp_ProPageResult`(IN tablename VARCHAR(1024), /*表名*/
IN tablecolumns VARCHAR(1024), /*查询字段*/
IN pagesize INT, /*每页记录数*/
IN currpage INT, /*当前页*/
IN order_field VARCHAR(100), -- 要排序的字段
IN asc_field INT, -- 排序值(1表示desc,0表示asc)
IN sCondition LONGTEXT,-- where条件
IN sGroupBy LONGTEXT,-- GROUP BY
OUT rowstotal INT)-- 返回记录数
COMMENT '分页存储过程'
BEGIN
DECLARE p_order_string VARCHAR(1000);
DECLARE p_where_string VARCHAR(1024);
DECLARE CURSQLCOUNT VARCHAR(3000);
/*定义变量*/
DECLARE m_begin_row INT DEFAULT 0;
DECLARE m_limit_string CHAR(64);
/*定义排序*/
IF asc_field = 1 THEN
SET p_order_string = CONCAT(' order by ', order_field, ' desc ');
ELSE
SET p_order_string = CONCAT(' order by ', order_field, ' asc ');
END IF;
/*定义where*/
IF sCondition IS NOT NULL AND sCondition <>'' THEN
SET p_where_string = CONCAT(' where ', sCondition);
ELSE
SET p_where_string = '';
END IF;
/*记录统计*/
SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', tablename, p_where_string);
IF (sGroupBy<>'') THEN
SET p_where_string=CONCAT(p_where_string,' ',sGroupBy);
END IF;
/*构造语句*/
SET m_begin_row = (currpage - 1) * pagesize;
SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', pagesize);
SET @MAIN_STRING = CONCAT('SELECT ', tablecolumns, ' FROM ', tablename, ' ', p_where_string, ' ', p_order_string, m_limit_string);
/*预处理*/
-- select @COUNT_STRING;
PREPARE CURSQLCOUNT FROM @COUNT_STRING;
EXECUTE CURSQLCOUNT;
SET rowstotal = @ROWS_TOTAL;
PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
END$$DELIMITER ;下面是ASP.NET调用存储过程:
public static DataTable SqlGetDataTable(string proc, CommandType type, string[] paramValue, out int OutTotalCount)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
DataSet ds = new DataSet();
using (MySqlCommand cmd = new MySqlCommand(proc, connection))
{
try
{
//分页开始
MySqlParameter[] myParms = new MySqlParameter[9];
myParms[0] = new MySqlParameter("@tablename", MySqlDbType.VarChar, 1024);
myParms[0].Value = paramValue[0];
myParms[1] = new MySqlParameter("@tablecolumns", MySqlDbType.VarChar, 1024);
myParms[1].Value = paramValue[1];
myParms[2] = new MySqlParameter("@pagesize", MySqlDbType.Int32, 5);
myParms[2].Value = paramValue[2];
myParms[3] = new MySqlParameter("@currpage", MySqlDbType.Int32, 5);
myParms[3].Value = paramValue[3];
myParms[4] = new MySqlParameter("@order_field", MySqlDbType.VarChar, 500);
myParms[4].Value = paramValue[4];
myParms[5] = new MySqlParameter("@asc_field", MySqlDbType.VarChar, 500);
myParms[5].Value = paramValue[5];
myParms[6] = new MySqlParameter("@sCondition", MySqlDbType.VarChar, 500);
myParms[6].Value = paramValue[6];
myParms[7] = new MySqlParameter("@sGroupBy", MySqlDbType.VarChar, 500);
myParms[7].Value = paramValue[7];
myParms[8] = new MySqlParameter("@rowstotal", MySqlDbType.Int32, 5);
myParms[8].Value = ParameterDirection.Output;
foreach (MySqlParameter parameter in myParms)
{
cmd.Parameters.Add(parameter);
}
cmd.CommandType = type;
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(ds);//这里出错:OUT or INOUT argument 9 for routine sosnet.sp_ProPageResult is not a variable or NEW pseudo-variable in BEFORE trigger
//out 总记录数
OutTotalCount = Convert.ToInt32(myParms[8].Value);
return ds.Tables[0];
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
}下面是Asp.NET的方法调用public static DataTable getPageList(string tableName, string sqlColumns, int size, int index, string orderColumns, int orderTag, string where, string sGroupBy, out int OutTotalCount)
{
DataTable dt = null;
//分页参数
string[] paramValue =
{
tableName,//表名
sqlColumns,//查询字段
""+size,//每页显示条数
""+index,//当前页
orderColumns,//要排序的字段
""+orderTag,//排序值(1表示desc,0表示asc)
where,//where条件
sGroupBy//group by (使用时需包含 group by)
};
dt = DbHelperMySQL.SqlGetDataTable("sp_ProPageResult", CommandType.StoredProcedure, paramValue, out OutTotalCount);
return dt;
}
int count;
DataTable dt = Maticsoft.Common.PagerList.getPageList("s_area", "*", 10, 1, "a_id", 1, "", "", out count);
PS:请问.net中调用mysql存储过程时 到底是用@还是? 有什么区别!!!虽然Mysql小 但个人很不喜欢!!!!!!!!感觉比MSSQL差远了MySQL存储ASP.NET分页
给你参考吧
我都写?的
还有就是最后一个应该不用传递的