对于开发人员来说，数据分页查询是常见的需求。网上也有一大堆的分页查询存储过程的例子，有的还冠以“通用”之名。但实际情况是，在特定项目需求下，又显得不那么通用了。最近在工作中就碰到连接多个表查询记录的情况，由于项目是基于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语句在执行时会报错。所以要将单个单引号替换成两个单引号。代码注释有说明。
大家如果有需要的的话，可以将参数和表连接查询的语句修改下以适应自己的应用场景。
