CREATE
PROC
[
dbo
]
.
[
GetRecordsPages
]
@pageIndex int = 1 ,
@pageSize int = 10 ,
@tblName nvarchar ( 600 ),
@fldName nvarchar ( 600 ),
@fldkey nvarchar ( 50 ),
@fldSort nvarchar ( 50 ),
@Groupby nvarchar ( 50 ),
@strWhere nvarchar ( 600 ),
@RecordsCount int OUTPUT
As
Begin
-- SET NOCOUNT ON
Declare @BeginIndex int
Declare @EndIndex int
Declare @Sql nvarchar ( 2000 )
Declare @GroupbyString nvarchar ( 200 )
Declare @OrderbyString nvarchar ( 200 )
if ( @strWhere = '' )
Set @strWhere = ' 1 = 1 '
Set @Sql = ' Select @totalCounts = Count(*) From ' + @tblName + ' Where ' + @strWhere + ''
exec sp_executesql @Sql , N ' @totalCounts int output ' , @RecordsCount output
Select @BeginIndex = ( @pageIndex - 1 ) * @pageSize
Select @EndIndex = @pageIndex * @pageSize
if ( @Groupby <> N '' )
Set @GroupbyString = N ' Group BY ' + @Groupby
else
Set @GroupbyString = N ' '
if ( @fldSort <> N '' )
Set @OrderbyString = N ' Order BY ' + @fldSort
else
Set @OrderbyString = N ' '
Set @Sql = N ' Select * From (Select ' + @fldName + ' , Row_Number() Over(Order By ' + @fldKey + ' ) As RowRank From ' + @tblName + ' Where ' + @strWhere + ' ) As TheTable Where RowRank > ' + Convert ( nvarchar ( 50 ), @BeginIndex ) + ' And RowRank <= ' + Convert ( nvarchar ( 50 ), @EndIndex ) + ' ' + @OrderbyString + ' ' + @GroupbyString + ''
print @Sql
exec ( @Sql )
End
GO
@pageIndex int = 1 ,
@pageSize int = 10 ,
@tblName nvarchar ( 600 ),
@fldName nvarchar ( 600 ),
@fldkey nvarchar ( 50 ),
@fldSort nvarchar ( 50 ),
@Groupby nvarchar ( 50 ),
@strWhere nvarchar ( 600 ),
@RecordsCount int OUTPUT
As
Begin
-- SET NOCOUNT ON
Declare @BeginIndex int
Declare @EndIndex int
Declare @Sql nvarchar ( 2000 )
Declare @GroupbyString nvarchar ( 200 )
Declare @OrderbyString nvarchar ( 200 )
if ( @strWhere = '' )
Set @strWhere = ' 1 = 1 '
Set @Sql = ' Select @totalCounts = Count(*) From ' + @tblName + ' Where ' + @strWhere + ''
exec sp_executesql @Sql , N ' @totalCounts int output ' , @RecordsCount output
Select @BeginIndex = ( @pageIndex - 1 ) * @pageSize
Select @EndIndex = @pageIndex * @pageSize
if ( @Groupby <> N '' )
Set @GroupbyString = N ' Group BY ' + @Groupby
else
Set @GroupbyString = N ' '
if ( @fldSort <> N '' )
Set @OrderbyString = N ' Order BY ' + @fldSort
else
Set @OrderbyString = N ' '
Set @Sql = N ' Select * From (Select ' + @fldName + ' , Row_Number() Over(Order By ' + @fldKey + ' ) As RowRank From ' + @tblName + ' Where ' + @strWhere + ' ) As TheTable Where RowRank > ' + Convert ( nvarchar ( 50 ), @BeginIndex ) + ' And RowRank <= ' + Convert ( nvarchar ( 50 ), @EndIndex ) + ' ' + @OrderbyString + ' ' + @GroupbyString + ''
print @Sql
exec ( @Sql )
End
GO
- #region 通用存储过程
- CREATE PROCEDURE [dbo].[DXBBS_CommonProc]
- @TableName varchar(50),
- @Condition nvarchar(500),
- @Fields varchar(500),
- @CurrentPage int,
- @PageSize int,
- @OrderField varchar(100),
- @OrderType varchar(4)
- AS
- set nocount on
- declare @SQL nvarchar(2000)
- if @OrderType = 'Desc'
- begin
- if @Condition = 'None'
- begin
- if @CurrentPage = 1
- begin
- set @SQL = 'select top ' + Cast(@PageSize as varchar) + ' ' + @Fields + ' from ' + @TableName + ' order by ' + @OrderField + ' desc'
- end
- if @CurrentPage > 1
- begin
- set @SQL = 'select top ' + Cast(@PageSize as varchar) + ' ' + @Fields + ' from ' + @TableName + ' where ' + @OrderField + '<(select min(' + @OrderField + ') from (select top ' + Cast(@PageSize*(@CurrentPage-1) as varchar) + ' ' + @OrderField + ' from ' + @TableName + ' order by ' + @OrderField + ' desc) temp) order by ' + @OrderField + ' desc'
- end
- end
- else
- begin
- if @CurrentPage = 1
- begin
- set @SQL = 'select top ' + Cast(@PageSize as varchar) + ' ' + @Fields + ' from ' + @TableName + ' ' + @Condition + ' order by ' + @OrderField + ' desc'
- end
- if @CurrentPage > 1
- begin
- set @SQL = 'select top ' + Cast(@PageSize as varchar) + ' ' + @Fields + ' from ' + @TableName + ' ' + @Condition + ' and ' + @OrderField + '<(select min(' + @OrderField + ') from (select top ' + Cast(@PageSize*(@CurrentPage-1) as varchar) + ' ' + @OrderField + ' from ' + @TableName + ' ' + @Condition + ' order by ' + @OrderField + ' desc) temp) order by ' + @OrderField + ' desc'
- end
- end
- end
- else
- begin
- if @Condition = 'None'
- begin
- if @CurrentPage = 1
- begin
- set @SQL = 'select top ' + Cast(@PageSize as varchar) + ' ' + @Fields + ' from ' + @TableName + ' order by ' + @OrderField + ' asc'
- end
- if @CurrentPage > 1
- begin
- set @SQL = 'select top ' + Cast(@PageSize as varchar) + ' ' + @Fields + ' from ' + @TableName + ' where ' + @OrderField + ' > (select max(' + @OrderField + ') from (select top ' + Cast(@PageSize*(@CurrentPage-1) as varchar) + ' ' + @OrderField + ' from ' + @TableName + ' order by ' + @OrderField + ' asc) temp) order by ' + @OrderField + ' asc'
- end
- end
- else
- begin
- if @CurrentPage = 1
- begin
- set @SQL = 'select top ' + Cast(@PageSize as varchar) + ' ' + @Fields + ' from ' + @TableName + ' ' + @Condition + ' order by ' + @OrderField + ' asc'
- end
- if @CurrentPage > 1
- begin
- set @SQL = 'select top ' + Cast(@PageSize as varchar) + ' ' + @Fields + ' from ' + @TableName + ' ' + @Condition + ' and ' + @OrderField + ' > (select max(' + @OrderField + ') from (select top ' + Cast(@PageSize*(@CurrentPage-1) as varchar) + ' ' + @OrderField + ' from ' + @TableName + ' ' + @Condition + ' order by ' + @OrderField + ' asc] temp) order by ' + @OrderField + ' asc'
- end
- end
- end
- exec sp_executesql @SQL
- #endregion