Sqlserver 2008 通用分页储存过程

注意:下面Tbl为TBL

?代码如下:

create procedure spc_pageShow

(

@tableName??? nvarchar(255),? ---表名

@primaryKeyField? nvarchar(50),--主键列名

@returnFileFieldLists? nvarchar(2000),--需要返回的列

@sortFieldLists? nvarchar(500),--需要排序的列(注意：该列在表中必须唯一,否则不能实现分页效果)

@pageSize int? =2,--每页显示的行数

@pageIndex? int =1,--当前页码

@orderType bit =0,--设置排序的类型(非0则降序)

@whereClause nvarchar(500)? ='' --查询条件(注意在下面的sql语句中不加where关键字)

)AS

BEGIN

?? DECLARE @strSQL nvarchar(4000),--主句

?????????????????????? @strSQLCount? nvarchar(1000),

?????????????????????? @strTmp? nvarchar(200),--临时变量

?????????????????????? @strOrder? nvarchar(500),--排序

?????????????????????? @InnerOrder?? nvarchar(600)

????Set ?@whereClause=IsNull(@whereClause,‘’);

????Set ?@InnerOrder=@SortFieldLists;

??? ----返回总页数

?? BEGIN

??????? IF (Len(LTrim(@whereClause))>0)

????????????? Set @strSQLCount? ='select (CASE WHEN Count(*) % '+Cast(@pageSize as nvarchar(30)) +'>0

?????????????????????????????????????????????????????????????????????????????? THEN count(*) / ' + cast(@pageSize as nvarchar(30)) +' +1

?????????????????????????????????????????????????????????????????????????????? ELSE Count(*) / ' + cast(@pageSize as nvarchar(30)) + '

?????????????????????????????????????????????????????????????????????????????? END) AS TotalCount from '+@tableName +' ; ' ;

?? END

?? EXECUTE sp_ExecuteSQL?@strSQLCount;

?

? BEGIN

?????? --设置排序字段

?????? /*

???????? IF (@orderType !=0)

?????????????????? Set @strOrder? ='? order by ' +@SortFieldLists + ' DESC';

??????????ELSE

?????????????????? Set @strSQL ='select Top ' + str(@pageSize) +' '? @parimaryKeyField? + ',' + @ReturnFieldLists +

????????????????? ' From ' +@tableName + 'order by '+@Innerorder;

????? */

???? ----根据页码提取行数据(如果第一页就执行以上代码,这样会加快执行速度)

??? IF (@pageIndex =1 )

??? BEGIN

????? IF (Len(@whereClause)? >0 )

??????? Set @strSQL =' Select Top ' + Str(@pageSize) +?' ' + @primaryKeyField + ',' + @ReturnFieldLists +

????????????????????????????????? 'From ' + @tableName +'where ' + @whereClause + 'order by '+@InnerOrder;

??????ELSE

??????????Set @strSQL ='Select TOP ' + str(@pageSize) + ' '? @primaryKeyField? + ','+ @ReturnFieldLists +

?????????????????????????????????????'From '? + @tableName? + ' order by' +@InnerOrder;

?? END

? ELSE

? BEGIN

????? DECLARE?? @Index? int,

?????????????????????????? @DotIndex?? int,

???????????????????????????@SortFieldTemp?? nvarchar(2000),

?????????????????????????? @strTemp?? nvarchar(200),

?????????????????????????? @strOrder1?? nvarchar(2000)

??????Set @strOrder1 = ''

????? WHILE (1=1)

?????? BEGIN

??????????????SELECT @Index =CHARINDEX (',',@SortFieldLists)

????????????? IF? @Index =0

?????????????? BEGIN

???????????????????? set @sortFieldTemp = @ SortFieldLists;

???????????????????? Select @DotIndex =CHARINDEX (' . ',@SortFieldTemp)

???????????????????? IF @DotIndex >0

????????????????????????? Set @strTemp =' tb1. ' + subString(@SortFieldTemp,@DotIndex +1,Len(@SortFieldTemp) - @DotIndex +1 )

???????????????????? ELSE

???????????????????????? Set @strTemp ='tb1. ' +@SortFieldTemp

?????????????????????Set @strOrder1 =@strOrder1 +','+@strTemp

???????????????????? --退出循环

?????????????????????BREAK;

????????????? END

?????????????ELSE

???????????? BEGIN

???????????????? Set @sortFieldTemp = subString(@sortFieldLists,1,@Index -1)

???????????????? Select @DotIndex =CHARINDEX ('。',@sortFieldTemp)

????????????????IF @DotIndex >0

?????????????????????? Set @strTemp ='tb1. ' + subString(@SortFieldTemp,Len(@sortFieldTemp)-@DotIndex +1)

????????????????ELSE

????????????????????? Set @strTemp ='tb. ' +@SortFieldTemp

?????????????? Set @strOrder1 = @strOrder1 + ',' + @strTemp

?????????????? --截取字符串

????????????? Select @SortFieldLists =subString (@sortFieldLists,@Index + 1,Len(@SortFieldLists) - @Index +1)

???????????? END

?????? END

????? ------截取第一个逗号

????? Select @strOrder1= subString (@strOrder,2,Len(@strOrder1))

?????? ---print @strOrder1

???????--return

????? /*

??????????? Select @Index =CHARINDEX ('。',@sortFieldLists )

????????????Set @Index =IsNull(@Index,0)

??????????? IF @Index >0

???????????????? Set @SortFieldLists =SubString (@SortFieldLists,@Index +1,Len(@SortFieldLists )-@Index +1)

????????????IF (@orderType !=0)

?????????????? Set @strOrder1 =' Order by Tb1. ' +@SortFieldLists + 'DESC ';

??????????? ELSE

?????????????? Set @strOrder1 =' order by Tb1. ' +@SortFieldLists + 'ASC ';

????? */

??? IF (Len(@whereClaus) > 0)

??????????Set @strSQL =' Select TOP ' + Str(@pageSize) + ' Tb1. *

???????????????????????????????????? From? (Select ' + @PrimaryKeyField + ' AS PrimaryKeyColumn,' + @ReturnFieldLists +

??????????????????????????????????????????????????? ' From ' + @TableName +' where ' + @WhereClause +' ) AS Tbl

??????????????????????????????????????????????????????where NOT EXISTS (Select PrimaryKeyColumn

????????????????????????????????????????????????????????????????????????????????????????????????? From???(Select TOP ' + Str(@pageSize * (@pageIndex -1 ) ) + ' ' + @PrimarykeyField + ' AS PrimaryKeyColumn?? From

????????????????????????????????????????????????????????????????????????????????????????????????????????????? ' +? @TableName + '

???????????????????????????????????????????????????????????????????????????????????????????????????????????????? where ' + @WhereClause + ' order By ' + @InnerOrder +

?????????????????????????????????????????????????????????????????????????????????????????????????????????? ' ) AS B

???????????????????????????????????????????????????????????????????????????????????????????????????????????? Where B.PrimaryKeyColumn =Tbl.PrimaryKeyColumn ) Order By '+@strOrder 1

??????ELSE

???????? Set @strSQL = ' Select TOP ' + Str(@pageSize) + ' Tbl.*

???????????????????????????????????????? From (Select? ' + @PrimaryKeyField + ' AS PrimaryKeyColumn,' + @ReturnFieldLists +

???????????????????????????????????????????????????????????????????? ' From ' + @TableName +

????????????????????????????????????????????????????????') AS Tbl

???????????????????????????????????????? Where NOT EXISTS (Select ' + @PrimaryKeyField + ' AS PrimaryKeyColumn

????????????????????????????????????????????????????????????? From ( Select? Top ' + Str(@pageSize * (@pageIndex -1 ) + ' ' + @PrimaryKeyField + ' AS PrimaryKeyColumn?? From ' + @TableName + 'Order by ' + @InnerOrder +

?????????????????????? ') AS B

???????????????????????Where B.PrimaryKeyColumn =Tb1.PrimaryKeyColumn ) Order by ' + @strOrder1

???????? END

----------------------------------------------------------------

? END

? ----print @strSQL;

?--动态执行SQL

? EXECUTE sp_ExecuteSQL @strSQL;

?END

GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!