SQL SERVER 2008分页存储过程，两种方式

　　对于开发人员来说，数据分页查询是常见的需求。网上也有一大堆的分页查询存储过程的例子，有的还冠以“通用”之名。但实际情况是，在特定项目需求下，又显得不那么通用了。最近在工作中就碰到连接多个表查询记录的情况，由于项目是基于Entity Framework的，最初的做法是先写好相关的视图，然后映射到EF，采用Linq To Entity查询数据。这样的做法运行效率低不说，每次有字段改动还得重新映射一遍，很郁闷。由于之前存储过程写的很少，不知道采用存储过程来实现分页查询。在网上参考了几个实例，再结合自己的项目，利用周末的时间写了个分页查询存储过程的模板。基本原理是存储过程的参数采用默认值，根据传进来的参数情况动态创建SQL语句，实现按条件查询。至于分页，有两种方法：SELECT TOP 方式和ROW_NUMBER()方式。前者是比较常见的处理方法，后者是SQL SERVER 2005才开始有的特性。

+ View Code?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101

Create PROCEDURE [dbo].[SP_QueryAttandance] (     @recordTotal INT OUTPUT,            --输出记录总数     @status int=0,     @empID int =0,     @dateFrom nvarchar(20)='',     @dateTo nvarchar(20)='',     @deptCode NVARCHAR(800)='',        --部门Code     @sName NVARCHAR(800) = '',        --员工名字     @sWorkNO NVARCHAR(200) = '',            --工号     @pageSize INT = 20,                    --每页记录数     @page INT =1,                    --当前页     @sortName NVARCHAR(50)='PKID',        --排序字段     @sortOrder NVARCHAR(20) = 'asc'        --排序顺序 )   AS BEGIN  declare @sql nvarchar(MAX)  declare @where nvarchar(MAX)=' where 1=1 ' --查询条件拼接字符串  declare @sort nvarchar(50) --排序信息  declare @cols nvarchar(MAX) --选择列  --表连接语句  set @sql = ' from TB_HR_Attendance as att left join [TB_Admin_User] as u on att.FK_EmpID = u.PK_ID ' +             'left join TB_Admin_Department dept on u.FK_DepartID = dept.PK_ID '+             ' left join TB_HR_DutyPeriod as duty on att.FK_DutyPeriodID = duty.PK_ID '  set @sort = ' order by '+ @sortName + ' ' + @sortOrder  set @cols = ' att.PK_ID as PKID,u.PK_ID as EmployeeID,u.sWorkNo as EmployeeNO, u.sName as EmployeeName, dept.sName as DepartName,dept.sCode as DeptCode, duty.sName as DutyPeriodName,duty.PK_ID as DutyPeriod,'+              'att.dtDate as Date,att.dtOnTime as OnTimeString, att.dtOffTime as OffTimeString,att.iStatus as Status '   IF @sName <> '' BEGIN     --如果字符串中包含单引号,则替换成转义后的符号,否则拼接的SQL会有语法错误     if(CHARINDEX('''',@sName)>0)     begin         set @sName = REPLACE(@sName,'''','''''')     end     set @where = @where + ' and EmployeeName like ''%'+@sName + '%''' END   IF @empID <>0 BEGIN     set @where = @where + ' and EmployeeID = '+ STR(@empID) END   IF @status = -1 BEGIN     set @where = @where + ' and Status > 1' END ELSE IF @status >0 BEGIN     set @where = @where + ' and Status = '+ STR(@status) END   IF @sWorkNO <> ''     --如果字符串中包含单引号,则替换成转义后的符号,否则拼接的SQL会有语法错误     if(CHARINDEX('''',@sWorkNO)>0)     begin         set @sWorkNO = REPLACE(@sWorkNO,'''','''''')     end BEGIN     set @where = @where + ' and EmployeeNO = '''+@sWorkNO +'''' END   IF @deptCode <> ''     --如果字符串中包含单引号,则替换成转义后的符号,否则拼接的SQL会有语法错误     if(CHARINDEX('''',@deptCode)>0)     begin         set @deptCode = REPLACE(@deptCode,'''','''''')     end BEGIN     set @where= @where+' and left(DeptCode,' + STR(LEN(@deptCode)) + ') =''' + @deptCode +'''' END   IF @dateFrom <> '' BEGIN     set @where = @where + ' and Date >= ''' + @dateFrom + '''' END   IF @dateTo <> '' BEGIN     set @where = @where + ' and Date <= '''+ @dateTo + '''' END --set @where = @where + ' and tmpid between' +str((@page - 1)*@pageSize + 1 )+' and '+str( @page * @pageSize) declare @strSQL nvarchar(MAX)     --采用SELECT TOP方式的分页 set @strSQL = 'select top ' + STR(@pageSize) + ' * from (select' +  @cols + @sql + ')as tmpTable3' + @where + ' and PKID not in (select top '+ STR((@page-1)*@pageSize) +  ' PKID from (select * from (select ' + @cols + @sql +')as tmpTable2' + @where  +')as tmpTable1' + @sort +')'  +' ' + @sort     --采用ROW_NUMBER()方法的分页  set @strSQL = N'select * from (select ROW_NUMBER() over(' + @sort + ') as tmpid, * from (select * from (select ' + @cols + @sql+ ')as tmpTable1' + @where +')as tmpTable2) as tmpTable3 '  +'where tmpid between' +str((@page - 1)*@pageSize + 1 )+' and '+str( @page * @pageSize)  print(@strSQL) exec(@strSQL)   set @strSQL = 'select @total= count(*) from (select' + @cols + @sql + ')as tmpTable' + @where print @strSQL exec sp_executesql @strSQL,N'@total int output',@total = @recordTotal output   END

　　总结下在写脚本过程中碰到的问题，我相信开发朋友们可能也碰到过。由于SQL语句用得不熟，很多语法都不太清楚，经过N次尝试和网上查询，总算能跑起来了。

　　1. 列别名问题。

　　SQL中为选择的列指定的别名在Order by子句中可以使用，但在Where子句中不能引用。一种解决办法是采用子查询，嵌套一层，在外层就可以引用别名，组织Where子句了。这对我写这个模板很关键，因为字段名是展现层表格的列名，排序用的也是这些列名。另外，需要给子查询结果表指定表别名，否则也会出错。这个让我当时纠结了半天，汗颜。

　　2.ROW_NUMBER()的用法

　　初次接触它，各种不熟悉。对于OVER(order by 列名 asc)，列别名同样不识别，可采用上面的方法，嵌套一层子查询来解决。

　　3.拼接SQL语句问题。

　　要实现按条件动态生成SQL，我采用的是拼接SQL。这样带来的问题跟SQL注入攻击的问题差不多。输入的参数如果带有一个单引号，拼接生成的SQL语句在执行时会报错。所以要将单个单引号替换成两个单引号。代码注释有说明。

　　大家如果有需要的的话，可以将参数和表连接查询的语句修改下以适应自己的应用场景。初次在园子写东西，还望大家多多指教！有什么问题和建议可以给我留言，谢谢。