[转载]支持定位当前页,自定义排序的分页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" > INT AS 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> |