MYSQL 從5.0開始便加入了存儲過程,因為它出現的時間還不是很久,現在有很多都在問,MySQL存儲過程怎麼建立,下我就我所理解的建了一個.在ASP.Net2.0下調用非常方法,跟SQL差不多,但也有少少差別,下面就是MYSQL一個分頁的的存儲過程;MySQL的limite給我們帶來很大的方便.不要寫太多的代碼!
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調用方法.下次給出來…….
http://www.cnblogs.com/jacklong/archive/2006/09/21/511127.Html
