怎么把SqlSever的存储过程改成MySql的存储过程 谁帮忙看下，我改了半天没出来。

[zczc1026741816]

修改前（SQLSVEVER的）：




create PROCEDURE [dbo].[QueryPagedData]
(
@TableName VARCHAR(200), ----待查询表名
@TableColumns nvarchar(500) = '*', ----待显示字段
@SqlCondition nvarchar(1000) = null, ----查询条件,不需where
@SqlSort nvarchar(500) = null, ----排序字段，不需order by
@PageIndex int, ----当前页
@PageSize int, ----每页显示的记录数
@TotalRecordCount int = 1 OUTPUT, ----返回总记录数
@TotalPageCount int = 1 OUTPUT ----返回总页数
)
AS
BEGIN
SET NOCOUNT ON; ----不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数。
Declare @sqlCountTmp nvarchar(1000) ----sql语句，查询数据总量
Declare @sqlDataTmp nvarchar(1000) ----sql语句，查询分页数据

----排序字段是否存在
if @SqlSort is null or @SqlSort=''
set @SqlSort = ' ObjectId ASC ' ----如果排序字段为空则默认升序排列

----查询条件是否存在
if @SqlCondition is null or @SqlCondition=''
set @SqlCondition = ' '
else
set @SqlCondition = ' where (1=1) ' + @SqlCondition ----类似于：select * from table where 1=1 and <查询语句>

----获取记录总数
set @sqlCountTmp = ' select @TotalRecordCount=Count(*) FROM '+@TableName + @SqlCondition
exec sp_executesql @sqlCountTmp,N'@TotalRecordCount int out ',@TotalRecordCount out ---- 执行带脚本的存储过程

----获取分页总数
declare @tmpCounts int
if @TotalRecordCount = 0 ----如果总记录数为0
set @tmpCounts = 1
else
set @tmpCounts = @TotalRecordCount
set @TotalPageCount=(@tmpCounts+@PageSize-1)/@PageSize
if @PageIndex>@TotalPageCount
set @PageIndex=@TotalPageCount

----返回数据查询
set @sqlDataTmp=' select '+@TableColumns+ ' from (select *,Row_number() over(order by '+@SqlSort+') as sqlRowIndex from '+ @TableName + @SqlCondition+') as TableWithRowIndex where sqlRowIndex>'+ cast(@PageSize*@PageIndex as Varchar(20))+' and sqlRowIndex<'+ cast((@PageSize*(@PageIndex+1)+1) as Varchar(20))
exec sp_executesql @sqlDataTmp
END








修改后（MYSQL）

DELIMITER $$

CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `lsd`.`QueryPagedData`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN

END$$

DELIMITER ;

CREATE PROCEDURE QueryPagedData(
IN TableName VARCHAR(200), /*待查询表名*/
IN TableColumns NVARCHAR(500) = '*', /*待显示字段*/
IN SqlCondition NVARCHAR(1000) = NULL, /*查询条件,不需where */
IN SqlSort NVARCHAR(500) = NULL, /*排序字段，不需order by */
OUT PageIndex INT, /*当前页*/
OUT PageSize INT, /*每页显示的记录数*/
OUT TotalRecordCount INT = 1 , /*返回总记录数*/
OUT TotalPageCount INT = 1 /*返回总页数 */
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT '分页存储过程'
BEGIN

/*定义变量*/
SET NOCOUNT ON;
DECLARE sqlCountTmp NVARCHAR(1000)
DECLARE sqlDataTmp NVARCHAR(1000)

/*排序字段是否存在*/
IF SqlSort IS NULL OR SqlSort=''
SET SqlSort = ' ObjectId ASC ' /*如果排序字段为空则默认升序排列*/

/*查询条件是否存在*/
IF SqlCondition IS NULL OR SqlCondition=''
SET SqlCondition = ' '
ELSE
SET SqlCondition = ' where (1=1) ' + SqlCondition /*类似于：select * from table where 1=1 and <查询语句>*/

/*获取记录总数*/
SET @sqlCountTmp = ' select @TotalRecordCount=Count(*) FROM '+TableName + SqlCondition
exec sp_executesql @sqlCountTmp,N'@TotalRecordCount int out ',TotalRecordCount OUT /*执行带脚本的存储过程*/

/*获取分页总数 */
DECLARE @tmpCounts INT
IF TotalRecordCount = 0 /*如果总记录数为0*/
SET tmpCounts = 1
ELSE
SET @tmpCounts = TotalRecordCount
SET TotalPageCount=(tmpCounts+PageSize-1)/PageSize
IF PageIndex>TotalPageCount
SET PageIndex=TotalPageCount

/*返回数据查询*/
SET @sqlDataTmp='select'+TableColumns+ 'from (select *,Row_number() over(order by '+SqlSort+') as sqlRowIndex from '+ TableName + SqlCondition+') as TableWithRowIndex where sqlRowIndex>'+ CAST(PageSize*PageIndex AS VARCHAR(20))+'and sqlRowIndex<'+ CAST((PageSize*(PageIndex+1)+1) AS VARCHAR(20))
exec sp_executesql @sqlDataTmp
END