[转载]支持定位当前页自定义排序的分页SQL(拒绝动态SQL)

[转载]支持定位当前页,自定义排序的分页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>
赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

登录

注册