Mikel
[转载]支持定位当前页,自定义排序的分页SQL(拒绝动态SQL) – 牟向阳 – 博客园.
1,场景：根据学生编号查询，返回该学生所在班级的所有学生。支持分页、自定义排序及结果集自动定位到查询条件的学生编号所在页。
<span class="pln">CREATE PROC </span><span class="pun">[</span><span class="pln">dbo</span><span class="pun">].[</span><span class="typ">Sp_testpagerandsorting</span><span class="pun">]</span><span class="pln"> </span><span class="pun">(</span><span class="lit">@GroupID</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@CurrentId</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@TimeFrom</span><span class="pln"> DATETIME</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@TimeTo</span><span class="pln"> DATETIME</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@OrderBy</span><span class="pln"> CHAR</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@PageSize</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@CurrentPage</span><span class="pln"> INT</span><span class="pun">)</span><span class="pln">AS SET nocount ON </span><span class="kwd">BEGIN</span><span class="pln"> DECLARE </span><span class="lit">@StartNumber</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@EndNumber</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@CurrentIdRowNumber</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@RecordCount</span><span class="pln"> INT</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@EndPageIndex</span><span class="pln"> INT DECLARE </span><span class="lit">@RowNumberTable</span><span class="pln"> TABLE </span><span class="pun">(</span><span class="pln"> rownumber INT IDENTITY </span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">),</span><span class="pln"> id INT </span><span class="pun">)</span><span class="pln"> </span><span class="pun">--</span><span class="pln">step </span><span class="lit">1</span><span class="pun">:</span><span class="pln"> </span><span class="typ">Build</span><span class="pln"> sort id list </span><span class="pun">-------------------------------------------------------</span><span class="pln"> INSERT INTO </span><span class="lit">@RowNumberTable</span><span class="pln"> </span><span class="pun">(</span><span class="pln">id</span><span class="pun">)</span><span class="pln"> SELECT sm</span><span class="pun">.</span><span class="pln">id AS id FROM dbo</span><span class="pun">.</span><span class="pln">test sm WITH </span><span class="pun">(</span><span class="pln">nolock</span><span class="pun">)</span><span class="pln"> WHERE indate BETWEEN </span><span class="typ">Coalesce</span><span class="pun">(</span><span class="lit">@TimeFrom</span><span class="pun">,</span><span class="pln"> indate</span><span class="pun">)</span><span class="pln"> AND </span><span class="typ">Coalesce</span><span class="pun">(</span><span class="lit">@TimeTo</span><span class="pun">,</span><span class="pln"> indate</span><span class="pun">)</span><span class="pln"> AND sm</span><span class="pun">.</span><span class="pln">groupid </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@GroupID</span><span class="pln"> ORDER BY CASE WHEN </span><span class="lit">@OrderBy</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'InDate desc'</span><span class="pln"> THEN </span><span class="pun">(</span><span class="pln"> </span><span class="typ">Row_number</span><span class="pun">()</span><span class="pln"> OVER </span><span class="pun">(</span><span class="pln">ORDER BY indate DESC</span><span class="pun">))</span><span class="pln"> WHEN </span><span class="lit">@OrderBy</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'InDate asc'</span><span class="pln"> THEN </span><span class="pun">(</span><span class="typ">Row_number</span><span class="pun">()</span><span class="pln"> OVER </span><span class="pun">(</span><span class="pln">ORDER BY indate ASC</span><span class="pun">))</span><span class="pln"> WHEN </span><span class="lit">@OrderBy</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Id asc'</span><span class="pln"> THEN </span><span class="pun">(</span><span class="typ">Row_number</span><span class="pun">()</span><span class="pln"> OVER </span><span class="pun">(</span><span class="pln">ORDER BY sm</span><span class="pun">.</span><span class="pln">id ASC</span><span class="pun">))</span><span class="pln"> WHEN </span><span class="lit">@OrderBy</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Id desc'</span><span class="pln"> THEN </span><span class="pun">(</span><span class="typ">Row_number</span><span class="pun">()</span><span class="pln"> OVER </span><span class="pun">(</span><span class="pln">ORDER BY sm</span><span class="pun">.</span><span class="pln">id DESC</span><span class="pun">))</span><span class="pln"> WHEN </span><span class="lit">@OrderBy</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Name asc'</span><span class="pln"> THEN </span><span class="pun">(</span><span class="typ">Row_number</span><span class="pun">()</span><span class="pln"> OVER </span><span class="pun">(</span><span class="pln">ORDER BY sm</span><span class="pun">.</span><span class="pln">name ASC</span><span class="pun">))</span><span class="pln"> WHEN </span><span class="lit">@OrderBy</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Name desc'</span><span class="pln"> THEN </span><span class="pun">(</span><span class="typ">Row_number</span><span class="pun">()</span><span class="pln"> OVER </span><span class="pun">(</span><span class="pln">ORDER BY sm</span><span class="pun">.</span><span class="pln">name DESC</span><span class="pun">)</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span><span class="pun">--</span><span class="pln">step </span><span class="lit">2</span><span class="pun">:</span><span class="pln"> </span><span class="typ">Reset</span><span class="pln"> page index </span><span class="kwd">with</span><span class="pln"> current id </span><span class="pun">-----------------------------------------</span><span class="pln"> IF </span><span class="lit">@CurrentIdNumber</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">BEGIN</span><span class="pln"> SELECT TOP </span><span class="lit">1</span><span class="pln"> </span><span class="lit">@CurrentIdRowNumber</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> rownumber FROM </span><span class="lit">@RowNumberTable</span><span class="pln"> WHERE id </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@CurrentIdNumber</span><span class="pln"> IF </span><span class="lit">@CurrentIdRowNumber</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">BEGIN</span><span class="pln"> IF </span><span class="lit">@CurrentPage</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">BEGIN</span><span class="pln"> SET </span><span class="lit">@CurrentPage</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Ceiling</span><span class="pun">(</span><span class="pln">CAST</span><span class="pun">(</span><span class="lit">@CurrentIdRowNumber</span><span class="pln"> AS DECIMAL</span><span class="pun">)</span><span class="pln"> </span><span class="pun">/</span><span class="pln"> CAST </span><span class="pun">(</span><span class="lit">@PageSize</span><span class="pln"> AS DECIMAL</span><span class="pun">))</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> ELSE </span><span class="kwd">BEGIN</span><span class="pln"> IF </span><span class="lit">@CurrentPage</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">BEGIN</span><span class="pln"> SET </span><span class="lit">@CurrentPage</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span><span class="pun">--</span><span class="pln">step </span><span class="lit">3</span><span class="pun">:</span><span class="pln"> </span><span class="typ">Set</span><span class="pln"> recordCount </span><span class="pun">-----------------------------------------</span><span class="pln"> SELECT </span><span class="lit">@RecordCount</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> COUNT</span><span class="pun">(</span><span class="lit">1</span><span class="pun">)</span><span class="pln"> FROM </span><span class="lit">@RowNumberTable</span><span class="pln"> </span><span class="pun">--</span><span class="pln">step </span><span class="lit">4</span><span class="pun">:</span><span class="pln"> </span><span class="typ">Calc</span><span class="pln"> startNumber </span><span class="pun">&</span><span class="pln"> endNumber </span><span class="pun">-----------------------------------------</span><span class="pln"> SELECT </span><span class="lit">@StartNumber</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@PageSize</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> </span><span class="lit">@CurrentPage</span><span class="pln"> </span><span class="pun">-</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="pun">),</span><span class="pln"> </span><span class="lit">@EndNumber</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@PageSize</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> </span><span class="lit">@CurrentPage</span><span class="pln"> </span><span class="pun">-</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="lit">@pageSize</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@EndPageIndex</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">Ceiling</span><span class="pun">(</span><span class="pln">CAST</span><span class="pun">(</span><span class="lit">@RecordCount</span><span class="pln"> AS DECIMAL</span><span class="pun">)</span><span class="pln"> </span><span class="pun">/</span><span class="pln"> CAST</span><span class="pun">(</span><span class="lit">@PageSize</span><span class="pln"> AS DECIMAL</span><span class="pun">))</span><span class="pln"> IF </span><span class="lit">@CurrentPage</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@EndPageIndex</span><span class="pln"> </span><span class="kwd">BEGIN</span><span class="pln"> SET </span><span class="lit">@EndNumber</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@RecordCount</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span><span class="pun">--</span><span class="pln">step </span><span class="lit">5</span><span class="pun">:</span><span class="pln"> </span><span class="typ">Get</span><span class="pln"> sorted id of current page </span><span class="pun">-----------------------------------------</span><span class="pln"> </span><span class="pun">;</span><span class="pln">WITH a AS </span><span class="pun">(</span><span class="pln">SELECT TOP </span><span class="pun">(</span><span class="lit">@EndNumber</span><span class="pln"> </span><span class="pun">-</span><span class="pln"> </span><span class="lit">@StartNumber</span><span class="pun">)</span><span class="pln"> id</span><span class="pun">,</span><span class="pln"> rownumber FROM </span><span class="pun">(</span><span class="pln">SELECT TOP </span><span class="pun">(</span><span class="lit">@EndNumber</span><span class="pun">)</span><span class="pln"> id</span><span class="pun">,</span><span class="pln"> rownumber FROM </span><span class="lit">@RowNumberTable</span><span class="pun">)</span><span class="pln"> AS b ORDER BY rownumber DESC</span><span class="pun">)</span><span class="pln"> </span><span class="pun">--</span><span class="pln">step </span><span class="lit">6</span><span class="pun">:</span><span class="pln"> </span><span class="typ">Return</span><span class="pln"> current page idList </span><span class="pun">-------------------------------------------------------</span><span class="pln"> SELECT </span><span class="pun">[</span><span class="pln">ID</span><span class="pun">],</span><span class="pln"> </span><span class="pun">[</span><span class="typ">GroupID</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="typ">Name</span><span class="pun">],</span><span class="pln"> </span><span class="pun">[</span><span class="typ">Address</span><span class="pun">]</span><span class="pln"> FROM dbo</span><span class="pun">.</span><span class="pln">test sm WITH</span><span class="pun">(</span><span class="pln">nolock</span><span class="pun">)</span><span class="pln"> INNER JOIN a ON a</span><span class="pun">.</span><span class="pln">id </span><span class="pun">=</span><span class="pln"> sm</span><span class="pun">.</span><span class="pln">id ORDER BY a</span><span class="pun">.</span><span class="pln">rownumber </span><span class="pun">--</span><span class="pln"> step </span><span class="lit">7</span><span class="pun">:</span><span class="kwd">return</span><span class="pln"> current page </span><span class="pun">&</span><span class="pln"> record count </span><span class="pun">----------------------------------</span><span class="pln"> SELECT </span><span class="lit">@CurrentPage</span><span class="pln"> AS currentpage</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@RecordCount</span><span class="pln"> AS recordcount </span><span class="kwd">END</span><span class="pln"> </span> |
2,简单条件的,动态where语句(关于Like查询的动态where，建议使用笨办法做)
<span class="pln">CREATE PROC </span><span class="pun">[</span><span class="pln">dbo</span><span class="pun">].[</span><span class="typ">Getstudentlistbycondition</span><span class="pun">]</span><span class="pln"> </span><span class="lit">@Name</span><span class="pln"> NVARCHAR</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@Class</span><span class="pln"> INTAS SET nocount ON </span><span class="kwd">BEGIN</span><span class="pln"> </span><span class="kwd">BEGIN</span><span class="pln"> SELECT </span><span class="pun">[</span><span class="typ">Name</span><span class="pun">],</span><span class="pln"> </span><span class="pun">[</span><span class="kwd">class</span><span class="pun">]</span><span class="pln"> FROM </span><span class="pun">[</span><span class="pln">testtable</span><span class="pun">]</span><span class="pln"> WHERE </span><span class="pun">[</span><span class="typ">Class</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> CASE WHEN </span><span class="lit">@Class</span><span class="pln"> </span><span class="pun">></span><span class="pln"> </span><span class="lit">0</span><span class="pln"> THEN </span><span class="lit">@Class</span><span class="pln"> ELSE </span><span class="pun">[</span><span class="typ">Class</span><span class="pun">]</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> AND </span><span class="pun">[</span><span class="pln">name</span><span class="pun">]</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> CASE WHEN </span><span class="lit">@Name</span><span class="pln"> </span><span class="pun"><></span><span class="pln"> </span><span class="str">''</span><span class="pln"> THEN </span><span class="lit">@Name</span><span class="pln"> ELSE </span><span class="pun">[</span><span class="typ">Name</span><span class="pun">]</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span> |
