MySQL 從5.0開始便加入了存儲過程,因為它出現(xiàn)的時間還不是很久,現(xiàn)在有很多都在問,MYSQL存儲過程怎么建立,下我就我所理解的建了一個.在asp.net2.0下調(diào)用非常方法,跟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調(diào)用方法.下次給出來…….

http://www.survivalescaperooms.com/jacklong/archive/2006/09/21/511127.html