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
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值