SQL Server 的通用分页显示存储过程_控控控-上ke控_新浪博客

ALTER PROCEDURE dbo.procListAllUser

(

@StrGetFields varchar(1000) ="*", 

@FieldName varchar(255),

@PageSize int = 10, 

@PageIndex int = 1, 

@TotalCount bit = 0, 

@OrderType bit = 0,

@StrWhere varchar(1500)

)

AS

declare @StrSQL varchar(5000) 

declare @StrTmp varchar(110) 

declare @StrOrder varchar(400) 

IF @TotalCount != 0

BEGIN

IF @StrWhere !=''''

SET @StrSQL = 'select count(*) as Total from [Users] where '+@StrWhere

ELSE

SET @StrSQL = 'select count(*) as Total from [Users]'

END 

ELSE

BEGIN

IF @OrderType != 0

BEGIN

SET @StrTmp = '<(select min'

SET @StrOrder = ' order by [' + @FieldName +'] desc'

END

ELSE

BEGIN

SET @StrTmp = '>(select max'

SET @StrOrder = ' order by [' + @FieldName +'] asc'

END

IF @PageIndex = 1

BEGIN

IF @StrWhere != '''' 

SET @StrSQL = 'select top ' + str(@PageSize) +' '+@StrGetFields+ '

from [Users] where ' + @StrWhere + ' ' + @StrOrder

ELSE

SET @StrSQL = 'select top ' + str(@PageSize) +' '+@StrGetFields+ ' 

from [Users] '+ @StrOrder

END

ELSE

BEGIN

SET @strSQL = 'select top' + str(@PageSize) +' '+@StrGetFields+ '

from [Users] where '+ @FieldName  + @strTmp + '(['+ @FieldName + ']) 

from (select top' + str((@PageIndex-1)*@PageSize) + ' ['+ @FieldName + '] 

from [Users]' + @StrOrder + ') as tblTmp)'+ @StrOrder

IF @StrWhere != ''''

SET @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [Users] where [' + @FieldName + ']' + @StrTmp + '(['

+ @FieldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

+ @FieldName + '] from [Users] where ' + @strWhere + ' '

+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

END 

END

print @StrSQL

exec @StrSQL

RETURN