其实很多非业务逻辑的功能，比如分页，数据过滤，可以在程序上面节省很多内存和CPU时间，但往往又找不到一个比较通用有效的方法，花了点时间，终于把我想要的在数据库中分页和过滤的功能写了出来，在这里分享。
第一期望：我希望输入页码（pageIndex），每页显示的记录数(pageSize)，然后这个存储过程就可以给我一个当前页的数据集。
首先，我假设有个表叫Configuration，里面就3个字段，Id, Key, Value，就是一个简单的数据表，定义如下：
CREATETABLE[dbo].[Configuration]([Id]intNOTNULLIDENTITY(1,1),[Key]varchar(20)NOTNULLUNIQUE,[Value]nvarchar(max)NULL)
然后，就可以写存储过程了，这里想要说下，我对存储过程命名的一些习惯，我会以"sp_"开头，为了标明这个存储过程是CRUD中的哪个操作，我会再附加一个缩写，比如这个分页的主要操作时READ，所以我的名字应该是"sp_r_"，然后为了说明它的主要功能是分页，就有了"sp_r_p_"，“p”表示paginate，最后跟上操作的主要的表名,"sp_r_p_configuration"。很多同学都喜欢用比如bypage等等，也可以，但是我更喜欢这种命名法，在特定的约定下，这种简洁性的命名可可读性。
再者，一个良好的代码书写规范是非常必要的，该注释的地方千万别省，在多数情况下，代码的可读性是非常重要的，除非因性能因素做必须得让步。
/*PROCEDURE: [dbo].[sp_r_p_configuration]OperaTION: READ
FEATURES: Paginate
PURPOSE: provide a paginated list from the configuration table
CREATOR: Jerry Weng
CREATETIME: 2014-4-9
UPDATETIME: 2014-4-9
VERSION: 1.0*/CREATEPROCEDURE[dbo].[sp_r_p_configuration]@pageIndexint=1,/*which page of the total page*/@pageSizeint=10,/*how many records show in one page*/@recordCountintOUTPUT,/*output: return the count of the total records in all pages*/@pageCountintOUTPUT/*output: return the count of the total pages*/ASdeclare@startRowint;--the row number of the first record in the pagedeclare@endRowint;--the row number of the last record in the pageset@startRow=(@pageIndex-1)*@pageSize+1;set@endRow=@startRow+@pageSize-1;
;withtmpas(select(ROW_NUMBER()over(orderbyId))asrow_id,[Id],[Key],[Value]fromdbo.Configuration)select[Id],[Key],[Value]fromtmpwhererow_idbetween@startRowand@endRow--calculate the record count in all pagesselect@recordCount=COUNT(*)fromdbo.Configuration--calculate the page countset@pageCount=CEILING(@recordCount/CAST(@pageSizeasfloat))RETURN0
这里用的ROW_NUMBER()来实现分页，性能必拼SQL的方法好，WITH...AS可以省下一个表变量。最后计算总记录数和页数很简单，算下就好了。
第二期望：这样的，似乎差不多了，但是我还希望能够有一套规则，来过滤结果集，并且还要有类似AND和OR的运算功能，而且我仍旧不希望用inline-script去实现，能不用尽量不要用。
为了实现这个期望，我需要先定义怎么传入过滤规则，我最终决定用的是xml数据类型而不是普通字符串，因为普通字符串有长度限制（如果你说可以用text，那我只能说太奢侈，小弟用不起），定短了可能被截断出现错误输出，定长了，最多也就8000个字符，也浪费，我不喜欢不确定因素，然而xml不一样，既是结构化数据，又有索引支持，可长可短，客户端也可直接序列化成字符串传入，非常方便。于是剩下的是，约定一个规则，确定过滤规则的数据结构，在这里我专门写了一个function来将xml转换成table已被后用。P.S. 如果想传一个数组到存储过程，也可以用xml类型，比字符串加分隔符更安全。
/*FUNCTION: [dbo].[fun_xmlfilter_parse]
OPERATION: READ
FEATURES:
PURPOSE: parse a table from a xml with the specific column name
CREATOR: Jerry Weng
CREATETIME: 2014-4-9
UPDATETIME: 2014-4-9
VERSION: 1.0*/CREATEFUNCTION[dbo].[fun_xmlfilter_parse](@filterxml,/*The format should be:
<filter>
<rule
column="COLUMN_NAME" -- the column name which the rule should be applied
match="xxx" -- the key which the rule defines to filter and the result should match
required="0|1" -- if 1, the result collection has to meet this rule or return nothing
/>
<rule ....
</filter>*/@columnvarchar(100)
)RETURNS@returntableTABLE([Match]nvarchar(100),[Required]bit)ASBEGIN;withtmpas(select[T].[RULE].value('@column','varchar(100)')as[Column],[T].[RULE].value('@match','nvarchar(100)')as[Match],[T].[RULE].value('@required','bit')as[Required]from@filter.nodes('filter//rule')[T]([RULE])
)INSERT@returntableselect[Match],[Required]fromtmpwhere[Column]=@columnRETURNEND
xml的结构由一个根节点<filter>开始，里面是多个<rule>节点，具体的规则定义在rule的属性列表中。column表示具体要过滤哪列的数据，match表示要匹配的数据值，比如我想要过滤列"Value"下，值为"sample"的数据，这样的话我只需要把xml写成<filter><rule column="value" match="sample" required="1" /></filter>，最后的哪个required就是来实现AND和OR的操作的，如果是1表示必须匹配，类似AND，0的话可选匹配，类似OR，输出的表示由COLUMN的值过滤过的，因为比较是以COLUMN为维度的。
最后，新建一个存储过程，叫"sp_r_fp_configuration"，这里比上一个多了一个"f"，表示带有过滤功能。
/*PROCEDURE: [dbo].[sp_r_fp_configuration]
OPERATION: READ
FEATURES: Paginate, Filter
PURPOSE: provide a paginated & filterable list from the configuration table
CREATOR: Jerry Weng
CREATETIME: 2014-4-9
UPDATETIME: 2014-4-9
VERSION: 1.0*/CREATEPROCEDURE[dbo].[sp_r_fp_configuration]@pageIndexint=1,/*which page of the total page*/@pageSizeint=10,/*how many records show in one page*/@filterxml,/*filter string for the records*/@recordCountintOUTPUT,/*output: return the count of the total records in all pages*/@pageCountintOUTPUT/*output: return the count of the total pages*/ASif(@filterisnull)beginexecdbo.sp_r_p_configuration@pageIndex,@pageSize,@pageCount=@pageCountOUTPUT,@recordCount=@recordCountOUTPUTendelsebegindeclare@tmptbltable(row_idint, Idint,[Key]varchar(100),[Value]varchar(200))declare@startRowint;--the row number of the first record in the pagedeclare@endRowint;--the row number of the last record in the pageset@startRow=(@pageIndex-1)*@pageSize+1;set@endRow=@startRow+@pageSize-1;insertinto@tmptblselect(ROW_NUMBER()over(orderbym.Id))asrow_id, m.[Id], m.[Key], m.[Value]fromdbo.Configurationasmouterapply fun_xmlfilter_parse(@filter,'key')askouterapply fun_xmlfilter_parse(@filter,'value')asvwhere(not(isnull(k.[Required],0)=0andisnull(v.[Required],0)=0)and(isnull(k.[Required],0)=0or(k.[Required]=1andisnull(m.[Key],'')=isnull(k.[Match],'')))and(isnull(v.[Required],0)=0or(v.[Required]=1andisnull(m.[Value],'')=isnull(v.[Match],'')))
)ork.[Required]=0andisnull(m.[Key],'')=isnull(k.[Match],'')orv.[Required]=0andisnull(m.[Value],'')=isnull(v.[Match],'')select[Id],[Key],[Value]from@tmptblwhererow_idbetween@startRowand@endRow--calculate the record count in all pagesselect@recordCount=COUNT(*)from@tmptbl--calculate the page countset@pageCount=CEILING(@recordCount/CAST(@pageSizeasfloat))endRETURN0
这里用了一个表变量，注意表变量是可被缓存的，临时表没有哦，用表变量不用with...as是因为后面在计算过滤后的总记录数的时候，还需用一次过滤后的集合。这里我希望列Key和列Value都可以被过滤，于是用了两个outer apply来将刚才那个函数的输出表附加到每行上，在where比较的地方，先比较Required是1的必要条件，当然如果针对某个column没有rule的话，也就是required is null的情况，是需要排除的，最后再附加连个required=0的可选条件，这样的话，一个select就可以把各种条件过滤出来了。不过，有个问题是，如果需要过滤的列比较多，那比较语句也要一条条加上去。
最后，来测试下：
我给表填充了这么些数据：
SETIDENTITY_INSERT[dbo].[Configuration]ONINSERT[dbo].[Configuration]([Id],[Key],[Value])VALUES(1, N'test1', N'123')INSERT[dbo].[Configuration]([Id],[Key],[Value])VALUES(2, N'test2', N'123')INSERT[dbo].[Configuration]([Id],[Key],[Value])VALUES(3, N'test3', N'567')INSERT[dbo].[Configuration]([Id],[Key],[Value])VALUES(4, N'test4',NULL)INSERT[dbo].[Configuration]([Id],[Key],[Value])VALUES(5, N'test5',NULL)INSERT[dbo].[Configuration]([Id],[Key],[Value])VALUES(6, N'test6',NULL)INSERT[dbo].[Configuration]([Id],[Key],[Value])VALUES(7, N'test7',NULL)INSERT[dbo].[Configuration]([Id],[Key],[Value])VALUES(8, N'test8',NULL)INSERT[dbo].[Configuration]([Id],[Key],[Value])VALUES(9, N'test9',NULL)INSERT[dbo].[Configuration]([Id],[Key],[Value])VALUES(10, N'test10',NULL)INSERT[dbo].[Configuration]([Id],[Key],[Value])VALUES(11, N'test11',NULL)SETIDENTITY_INSERT[dbo].[Configuration]OFF
测试脚本：
declare@pageIndexint;set@pageIndex=1;declare@pageSizeint;set@pageSize=5;declare@filterxml;set@filter='<filter><rule column="key" match="test3" required="1"/><rule column="value" match="123" required="0"/></filter>'declare@pageCountint;declare@recordCountint;execdbo.sp_r_fp_configuration@pageIndex,@pageSize,@filter,@pageCount=@pageCountOUTPUT,@recordCount=@recordCountOUTPUTselect@pageCountas[PAGECOUNT],@recordCountas[RECORDCOUNT]
输出，如果需要找null值的话，直接把match属性去掉就可以了<rule column="value" required="1"/>：