分页存储过程代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】分页存储过程
作者:Dezai.CN / 发布于2011/3/2/ 1200
<div><span style="color: #008080">/*</span><span style="color: #008080">
 函数名称: GetRecordFromPage
 函数功能: 获取指定页的数据
 参数说明: @tblName 包含数据的表名
 @fldName 关键字段名
 @PageSize 每页记录数
 @PageIndex 要获取的页码
 @OrderType 排序类型, 0 - 升序, 1 - 降序
 @strWhere 查询条件 (注意: 不要加 where)
</span><span style="color: #008080">*/</span><span style="color: #000000">
</span><span style="color: #0000ff">CREATE</span><span style="color: #000000"> </span><span style="color: #0000ff">PROCEDURE</span><span style="color: #000000"> GetRecordFromPage
 </span><span style="color: #008000">@tblName</span><span style="color: #000000"> </span><span style="color: #0000ff">varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">255</span><span style="color: #000000">), </span><span style="color: #008080">--</span><span style="color: #008080"> 表名</span><span style="color: #008080">
</span><span style="color: #000000"> </span><span style="color: #008000">@fldName</span><span style="color: #000000"> </span><span style="color: #0000ff">varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">255</span><span style="color: #000000">), </span><span style="color: #008080">--</span><span style="color: #008080"> 字段名</span><span style="color: #008080">
</span><span style="color: #000000"> </span><span style="color: #008000">@PageSize</span><span style="color: #000000"> </span><span style="color: #0000ff">int</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">10</span><span style="color: #000000">, </span><span style="color: #008080">--</span><span style="color: #008080"> 页尺寸</span><span style="color: #008080">
</span><span style="color: #000000"> </span><span style="color: #008000">@PageIndex</span><span style="color: #000000"> </span><span style="color: #0000ff">int</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">, </span><span style="color: #008080">--</span><span style="color: #008080"> 页码</span><span style="color: #008080">
</span><span style="color: #000000"> </span><span style="color: #008000">@OrderType</span><span style="color: #000000"> </span><span style="color: #0000ff">bit</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">, </span><span style="color: #008080">--</span><span style="color: #008080"> 设置排序类型, 非 0 值则降序</span><span style="color: #008080">
</span><span style="color: #000000"> </span><span style="color: #008000">@strWhere</span><span style="color: #000000"> </span><span style="color: #0000ff">varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">2000</span><span style="color: #000000">) </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff0000">''</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080"> 查询条件 (注意: 不要加 where)</span><span style="color: #008080">
</span><span style="color: #0000ff">AS</span><span style="color: #000000">

</span><span style="color: #0000ff">declare</span><span style="color: #000000"> </span><span style="color: #008000">@strSQL</span><span style="color: #000000"> </span><span style="color: #0000ff">varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">6000</span><span style="color: #000000">) </span><span style="color: #008080">--</span><span style="color: #008080"> 主语句</span><span style="color: #008080">
</span><span style="color: #0000ff">declare</span><span style="color: #000000"> </span><span style="color: #008000">@strTmp</span><span style="color: #000000"> </span><span style="color: #0000ff">varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">1000</span><span style="color: #000000">) </span><span style="color: #008080">--</span><span style="color: #008080"> 临时变量</span><span style="color: #008080">
</span><span style="color: #0000ff">declare</span><span style="color: #000000"> </span><span style="color: #008000">@strOrder</span><span style="color: #000000"> </span><span style="color: #0000ff">varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">500</span><span style="color: #000000">) </span><span style="color: #008080">--</span><span style="color: #008080"> 排序类型</span><span style="color: #008080">
</span><span style="color: #000000">
</span><span style="color: #0000ff">if</span><span style="color: #000000"> </span><span style="color: #008000">@OrderType</span><span style="color: #000000"> </span><span style="color: #808080">!=</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">
</span><span style="color: #0000ff">begin</span><span style="color: #000000">
 </span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@strTmp</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000"><(select min</span><span style="color: #ff0000">'</span><span style="color: #000000">
 </span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@strOrder</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000"> order by [</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@fldName</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">] desc</span><span style="color: #ff0000">'</span><span style="color: #000000">
</span><span style="color: #0000ff">end</span><span style="color: #000000">
</span><span style="color: #0000ff">else</span><span style="color: #000000">
</span><span style="color: #0000ff">begin</span><span style="color: #000000">
 </span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@strTmp</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">>(select max</span><span style="color: #ff0000">'</span><span style="color: #000000">
 </span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@strOrder</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000"> order by [</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@fldName</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #ff0000">'</span><span style="color: #ff0000">] asc</span><span style="color: #ff0000">'</span><span style="color: #000000">
</span><span style="color: #0000ff">end</span><span style="color: #000000">

</span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@strSQL</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">select top </span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff00ff">str</span><span style="color: #000000">(</span><span style="color: #008000">@PageSize</span><span style="color: #000000">) </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000"> * from [</span><span style="color: #ff0000">'</span><span style="color: #000000">
 </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@tblName</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">] where [</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@fldName</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">]</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@strTmp</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">([</span><span style="color: #ff0000">'</span><span style="color: #000000">
 </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@fldName</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">]) from (select top </span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff00ff">str</span><span style="color: #000000">((</span><span style="color: #008000">@PageIndex</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)</span><span style="color: #808080">*</span><span style="color: #008000">@PageSize</span><span style="color: #000000">) </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000"> [</span><span style="color: #ff0000">'</span><span style="color: #000000">
 </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@fldName</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">] from [</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@tblName</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">]</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@strOrder</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">) as tblTmp)</span><span style="color: #ff0000">'</span><span style="color: #000000">
 </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@strOrder</span><span style="color: #000000">

</span><span style="color: #0000ff">if</span><span style="color: #000000"> </span><span style="color: #008000">@strWhere</span><span style="color: #000000"> </span><span style="color: #808080">!=</span><span style="color: #000000"> </span><span style="color: #ff0000">''</span><span style="color: #000000">
 </span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@strSQL</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">select top </span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff00ff">str</span><span style="color: #000000">(</span><span style="color: #008000">@PageSize</span><span style="color: #000000">) </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000"> * from [</span><span style="color: #ff0000">'</span><span style="color: #000000">
 </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@tblName</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">] where [</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@fldName</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">]</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@strTmp</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">([</span><span style="color: #ff0000">'</span><span style="color: #000000">
 </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@fldName</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">]) from (select top </span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff00ff">str</span><span style="color: #000000">((</span><span style="color: #008000">@PageIndex</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">)</span><span style="color: #808080">*</span><span style="color: #008000">@PageSize</span><span style="color: #000000">) </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000"> [</span><span style="color: #ff0000">'</span><span style="color: #000000">
 </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@fldName</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">] from [</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@tblName</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">] where </span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@strWhere</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000"> </span><span style="color: #ff0000">'</span><span style="color: #000000">
 </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@strOrder</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">) as tblTmp) and </span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@strWhere</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000"> </span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@strOrder</span><span style="color: #000000">

</span><span style="color: #0000ff">if</span><span style="color: #000000"> </span><span style="color: #008000">@PageIndex</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">
</span><span style="color: #0000ff">begin</span><span style="color: #000000">
 </span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@strTmp</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff0000">''</span><span style="color: #000000">
 </span><span style="color: #0000ff">if</span><span style="color: #000000"> </span><span style="color: #008000">@strWhere</span><span style="color: #000000"> </span><span style="color: #808080">!=</span><span style="color: #000000"> </span><span style="color: #ff0000">''</span><span style="color: #000000">
 </span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@strTmp</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000"> where (</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@strWhere</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">)</span><span style="color: #ff0000">'</span><span style="color: #000000">

 </span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@strSQL</span><span style="color: #000000"> </span><span style="color: #808080">=</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">select top </span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff00ff">str</span><span style="color: #000000">(</span><span style="color: #008000">@PageSize</span><span style="color: #000000">) </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000"> * from [</span><span style="color: #ff0000">'</span><span style="color: #000000">
 </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@tblName</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">]</span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@strTmp</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000"> </span><span style="color: #ff0000">'</span><span style="color: #000000"> </span><span style="color: #808080">+</span><span style="color: #000000"> </span><span style="color: #008000">@strOrder</span><span style="color: #000000">
</span><span style="color: #0000ff">end</span><span style="color: #000000">

</span><span style="color: #0000ff">exec</span><span style="color: #000000"> (</span><span style="color: #008000">@strSQL</span><span style="color: #000000">)

</span><span style="color: #0000ff">GO</span><span style="color: #000000">
</span></div>
试试其它关键字
　分页　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)