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

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

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语句在执行时会报错。所以要将单个单引号替换成两个单引号。代码注释有说明。

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