USE [USInfo]
GO
/****** 对象: StoredProcedure [dbo].[ent_Pages] 脚本日期: 10/26/ 15:33:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ent_Pages]
(
@Primaryid varchar(100),
@TableName varchar(1000),
@FieldList varchar(2000),
@StrWhere varchar(2000),
@OrderField varchar(1000),
@PageIndex int,
@PageSize int,
@PageCount int output,
@RecordCount int output
)
AS
SET NOCOUNT ON
DECLARE @intRootRecordCount int
DECLARE @SqlQuery nvarchar(4000)
SET @PageCount=0
SET @SqlQuery= 'SET NOCOUNT ON;SELECT @SPintRootRecordCount=Count(*) FROM '+@TableName+' WHERE '+@StrWhere
EXECUTE sp_executesql @SqlQuery,N'@SPintRootRecordCount int OUTPUT',@SPintRootRecordCount=@intRootRecordCount OUTPUT
SELECT @RecordCount = @intRootRecordCount
IF @RecordCount=0
return
ELSE
BEGIN
SELECT @PageCount=(@RecordCount+@PageSize-1)/@PageSize
IF @PageIndex=1
IF @StrWhere!=''
SELECT @SqlQuery='SELECT TOP '+CAST(@PageSize AS VARCHAR(20))+' '+@FieldList+' FROM ' +@TableName+' WHERE '+@StrWhere +' ORDER BY '+@OrderField
ELSE
SELECT @SqlQuery='SELECT TOP '+CAST(@PageSize AS VARCHAR(20))+' '+@FieldList+' FROM ' +@TableName+' ORDER BY '+@OrderField
ELSE
SET @SqlQuery='SELECT '+@FieldList+' FROM '+@TableName+' WHERE '+@StrWhere +' AND a.'+@Primaryid+' in(SELECT '+@Primaryid+' FROM (SELECT row_number() over(ORDER BY '+@OrderField+') as rownum,a.'+@Primaryid+' FROM '+@TableName+' WHERE '+@StrWhere +') as temptbl
WHERE rownum BETWEEN ('+cast(@PageIndex as varchar)+'-1)*'+cast(@PageSize as varchar)+'+1 and '+cast(@PageIndex as varchar)+'*'+cast(@PageSize as varchar) + ') ORDER BY '+@OrderField
END
SET NOCOUNT ON
EXECUTE(@SqlQuery)
SET NOCOUNT OFF
望ORACLE高手帮忙改写
