MySql分页存储过程转换成sqlserver分页存储过程
下面是Mysql的分页存储过程,帮忙转换成sqlserver的。我自己转了下,结果是运行没效果。
求助。
CREATE PROCEDURE `Search_SplitPage_TemplateTable`(PageIndex int,PageSize int,FromTables varchar(512),WhereField varchar(1024),IndexPageSelect varchar(256),SelectField varchar(512),GroupField varchar(512),HavingField varchar(512),OverOrderField varchar(512))
BEGIN
declare pageIndexsqlString nvarchar(3000);
declare sqlString nvarchar(3000);
declare whereString nvarchar(3000);
declare fromString nvarchar(512);
declare groupString nvarchar(512);
declare havingString nvarchar(512);
declare orderString nvarchar(512);
declare whereAddtionString nvarchar(256);
DECLARE PageLowerBound int;
DECLARE PageUpperBound int;
DECLARE TotalRecords int;
SET PageLowerBound = PageSize * (PageIndex-1) + 1;
SET PageUpperBound = PageSize - 1 + PageLowerBound;
SET fromString= CONCAT(' from ', FromTables);
SET WhereField=ifnull(WhereField,'');
IF WhereField<>'' then
SET whereString=CONCAT(' where ',WhereField);
SET whereAddtionString = CONCAT(' and ',IndexPageSelect,CONCAT('=pindex.ID and pindex.IndexID >=',LTRIM(CONCAT(PageLowerBound))),CONCAT(' AND pindex.IndexID <=',LTRIM(CONCAT(PageUpperBound))));
ELSE
SET whereString='';
SET whereAddtionString = CONCAT(' where ',IndexPageSelect,CONCAT('=pindex.ID and pindex.IndexID >=',LTRIM(CONCAT(PageLowerBound))),CONCAT(' AND pindex.IndexID <=',LTRIM(CONCAT(PageUpperBound))));
END if;
SET OverOrderField=ifnull(OverOrderField,'');
IF OverOrderField<>'' then
SET orderString= CONCAT(' order by ', OverOrderField);
ELSE
SET orderString='';
END if;
SET GroupField=ifnull(GroupField,'');
IF GroupField<>'' then
SET groupString= CONCAT(' group by ', GroupField);
SET HavingField= ifnull(HavingField,'');
IF HavingField<> '' then
SET havingString= CONCAT(' having ', HavingField);
ELSE
SET havingString='';
end if;
ELSE
SET groupString= '';
SET havingString='';
END if;
SET @stmppageIndexsqlString =
CONCAT('INSERT INTO PageIndex(ID) ','select ',IndexPageSelect,fromString,whereString,orderString,groupString,havingString);
drop table if exists PageIndex;
create TEMPORARY table PageIndex
(
IndexID int(11) auto_increment not null primary key,
ID int(11)
);
prepare pageIndexsqlString from @stmppageIndexsqlString;
EXECUTE pageIndexsqlString;
SET @stmpsqlString =
CONCAT('select ',SelectField,fromString,
', PageIndex pindex ',whereString,whereAddtionString,orderString,groupString,havingString);
prepare sqlString from @stmpsqlString;
EXECUTE sqlString;
SELECT COUNT(*) into TotalRecords
FROM PageIndex;
SELECT TotalRecords;
END
尽量不增加修改变量哦,谢谢大神啦