MYSQL has added stored procedures since 5.0, because it has not been around for a long time. Now many people are asking how to create a MYSQL stored procedure. I will build one based on my understanding. Under asp.net2.0 Calling extraordinary methods is similar to SQL, but there are some differences. The following is a paging stored procedure of MYSQL; MYSQL's limit brings us great convenience. Don't write too much code!
DELIMITER $$;
DROP PROCEDURE IF EXISTS `mytest`.`MyPage`$$
CREATE DEFINER=`root`@`localhost ` PROCEDURE `MyPage`(
tableName varchar(100),
fieldsName VARCHAR(100),
pageIndex int,
pageSize int,
sortName VARCHAR(500),
strWhere varchar(500)
)
BEGIN
DECLARE fieldlist VARCHAR( 200);
if fieldsName=''||fieldsName=null THEN
set fieldlist='*';
else
set fieldlist=fieldsName;
end if;
if strWhere=''||strWhere=null then
if sortName=''||sortName= null then
set @strSQL=concat('select ',fieldlist,' from ' , tableName,' limit ',(pageindex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('select ', fieldlist,' from ', tableName,' order by ',sortName,' limit ',(pageindex-1)*pageSize,',',pageSize);
end if;
else
if sortName=''||sortName=null then
set @strSQL=concat('select ',fieldlist,' from ' , tableName,' where ',strWhere,' limit ',(pageindex-1)*pageSize,',',pageSize);
else
set @strSQL=concat(' select ',fieldlist,' from ' , tableName,' where ',strWhere,' order by ',sortName,' limit ',(pageindex-1)*pageSize,',',pageSize);
end if;
end if;
PREPARE stmt1 FROM @strSQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;$$
Asp.net2.0 calling method. It will be given next time...
http://www.cnblogs.com/jacklong/archive/2006/09/21/511127.html