By using this site, you acknowledge that you have read and understand our Cookie policy, Privacy policy and Terms .
close

个人感觉这个存储过程写得比较的好:

转载自:http://www.cnblogs.com/eme/archive/2006/12/19/597275.html

/*
过程说明:对表按任意字段进行排序分页
创建时间:2005年7月24日
作者: chen gang
*/
CREATE PROCEDURE dbo.GetPagingRecord
    (
        @tablename varchar(100),--表名或视图表
        @fieldlist varchar(4000)='*',--欲选择字段列表
        @orderfield varchar(100),--排序字段
        @keyfield varchar(100),--主键
        @pageindex int,--页号,从1开始
        @pagesize int=20,--页尺寸
        @strwhere varchar(4000),--条件
        @ordertype varchar(1)='1'--排序,1,降序,0,升序
    )
AS

    SET NOCOUNT ON
    declare @sqlstr varchar(6000)
    declare @orderstr varchar(100)
    if(@keyfield=@orderfield)
    begin
set @orderstr = ''
    end
    else
    begin
set @orderstr = ','+@keyfield+' asc'
    end
    if @pageindex<=0
set @pageindex=1
    --处理SQL中危险字符,并且将条件处理成易嵌入的形式
    set @strwhere=replace(@strwhere,'''','''''')
    set @strwhere=replace(@strwhere,'--','')
    set @strwhere=replace(@strwhere,';','')
    set @sqlstr='declare @CurPageNum int;'
    set @sqlstr=@sqlstr+'declare @nextpagenum int;'
    set @sqlstr=@sqlstr+'set @curpagenum='+cast(@PageIndex-1 as varchar)+'*'+cast(@Pagesize as varchar)+';'
    set @sqlstr=@sqlstr+'set @nextpagenum='+cast(@PageIndex as varchar)+'*'+cast(@Pagesize as varchar)+';'
    set @sqlstr=@sqlstr+'declare @sqlstr varchar(6000);'
    if @ordertype='1'
    begin
    set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from

'+@tablename+' where 1=1 '+@strwhere+' order by '+@orderfield+' desc' + @orderstr+') as a where 1=1 and '+@keyfield+' not in (

select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where 1=1 '+@strwhere+'

order by '+@orderfield +' desc'+ @orderstr +') order by ' + @orderfield +' desc'';'
    end
    else
    begin
    set @sqlstr=@sqlstr+'set @sqlstr=''select '+@fieldlist+' from ( select top ''+cast(@nextpagenum as varchar)+'' * from

'+@tablename+' where 1=1 '+@strwhere+' order by '+@orderfield+' asc' + @orderstr+' ) as a where 1=1 and '+@keyfield+' not in (

select top ''+cast(@curpagenum as varchar)+'' '+@keyfield+' from '+@tablename+' where 1=1 '+@strwhere+'

order by '+@orderfield +' asc' + @orderstr+') order by '+@orderfield +' asc'';'
    end
    set @sqlstr=@sqlstr+'execute( @sqlstr)'
    print @sqlstr
execute(@sqlstr)
GO

info Last modified by Raymond at 6 years ago
info About author

info License/Terms

More from Kontext

local_offer SQL Server local_offer hive

visibility 1102
comment 0
thumb_up 0
access_time 10 months ago

In one of my previous post, I showed how to configure Apache Hive 3.0.0 in Windows 10. Apache Hive 3.0.0 Installation on Windows 10 Step by Step Guide ...

open_in_new View

local_offer SQL Server local_offer Java local_offer kerberos local_offer NTLM

visibility 1200
comment 0
thumb_up 0
access_time 9 months ago

With Microsoft SQL Server JDBC driver, you can connect to the database through SQL Server Authentication or Kerberos Authentication. This post summarizes the configurations required for each authentication method with coding examples. *NTLM block in the following diagram represents pure Jav...

open_in_new View

local_offer SQL Server local_offer python local_offer spark local_offer pyspark

visibility 8196
comment 0
thumb_up 1
access_time 11 months ago

Spark is an analytics engine for big data processing. There are various ways to connect to a database in Spark. This page summarizes some of common approaches to connect to SQL Server using Python as programming language. ...

open_in_new View

local_offer SQL Server local_offer lite-log

visibility 232
comment 0
thumb_up 0
access_time 2 years ago

If you have used Visual Studio Code, you probably will also enjoy a new client tool SQL Operations Studio. Currently it is available as preview version and not formally released yet. What is SQL Operations Studio SQL Operations Studio is a data management tool that enables working w...

open_in_new View