???????????oracle??????о?oracle????????????????????????????????SQL SERVICE?????????1????????????????????д????SQL???洢??????????о???oracle???????????????о?????????
????????????洢????????????????????????????????????д?????????????????????о??????????????ò???????????????????
???????????????????
????GO
IF (SELECT COUNT(*) FROM sysobjects s WHERE s.[type]='P' AND s.name='SP_PAGE')>0
DROP PROC SP_PAGE
GO
CREATE PROC SP_PAGE(@tbName VARCHAR(50)??@Condition NVARCHAR(1000)??@SortColumn VARCHAR(100)?? @isDesc INT??@thisPage INT??@PageRowNumber INT??@sumPage INT OUT)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @exeSql NVARCHAR(1000) ;
DECLARE @sumRowNumber INT??@descStr NVARCHAR(10);
SET @exeSql='select @sumRowNumber=count(*) from '+@tbName+' '+@Condition;
--??ж??SQL????
EXEC sp_executesql  @exeSql?? N'@sumRowNumber int out'??@sumRowNumber OUT;
--?????????
SET @sumPage=(@sumRowNumber-1)/@PageRowNumber+1;
--????????
DECLARE @BeginRow INT??@EndRow INT;
SET @BeginRow=(@thisPage-1)*@PageRowNumber+1;
SET @EndRow=@thisPage*@PageRowNumber;
--????????
IF @isDesc=1
BEGIN
SET @descStr='desc';
END
ELSE
BEGIN
SET @descStr='asc';
END
SET @exeSql='select * from (select a.*??ROW_NUMBER() OVER(order by '+@SortColumn+' '+@descStr+ ') as rowIndex from '+@tbName+' a '+@Condition+') a where a.rowIndex BETWEEN '+CONVERT(VARCHAR(10)??@BeginRow)+' AND '+CONVERT(VARCHAR(10)??@EndRow)+'';
EXEC(@exeSql);
--????????????
if @@ERROR>0
BEGIN
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
COMMIT TRANSACTION;
END;
END