|M| 本人SQL很菜 我一直用的一条分页存储过程感觉要修改一下,哪位大哥帮帮 还有写一下注释 我好学习 200分 谢谢

Question

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GOALTER   PROCEDURE My_Page  @TB         VARCHAR(1000), 
  @COL       VARCHAR(1000),
--  @COLTYPE     INT, 
  @ORDERBY  BIT,       
  @COLLIST     VARCHAR(3000),
  @PAGESIZE   INT,     
  @PAGE       INT,  
  @CONDITION   VARCHAR(1000),
  @IfQueryCount bit,
  @RecCount INT OUTPUT,
  @PAGES     INT OUTPUT,
  @OUTSQL NVARCHAR(4000) OUTPUTASDECLARE @SQL NVARCHAR(4000)
DECLARE @WHERE1 VARCHAR(800)
DECLARE @WHERE2 VARCHAR(800)Create table #tmpkeylist
(
pkid int
)IF @CONDITION IS NULL OR  RTRIM(@CONDITION) = ''
BEGIN
   SET @WHERE1=' WHERE '
   SET @WHERE2='  '
END
ELSE
BEGIN
   SET @WHERE1=' WHERE ('+@CONDITION+') AND '
   SET @WHERE2=' WHERE ('+@CONDITION+') '
END--SET @SQL = 'SELECT @RecCount=COUNT(*),@PAGES=CEILING((+0.0)/'+CAST(@PAGESIZE AS VARCHAR)+') FROM '+@TB+@WHERE2
SET @SQL = 'SELECT @RecCount=COUNT(*),@PAGES=CEILING(COUNT(*)*1.0/'+CAST(@PAGESIZE AS VARCHAR)+') FROM '+@TB+@WHERE2IF(@IfQueryCount=1)
EXEC SP_EXECUTESQL @SQL,N'@PAGES INT OUTPUT,@RecCount INT OUTPUT',@PAGES OUTPUT,@RecCount OUTPUT
ELSE
SELECT @PAGES = 0,@RecCount=0IF @ORDERBY=0
begin
  SET @SQL = ' insert into #tmpkeylist(pkid)   SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR) +  @COL+' FROM '+@TB+@WHERE2+' ORDER BY '+@COL
  EXEC(@SQL)
  SET @SQL= 'SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'>(SELECT MAX(pkid) '+' FROM #tmpkeylist) ORDER BY '+@COL
end
ELSE
begin
  SET @SQL = 'insert into #tmpkeylist(pkid)  SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR) +  @COL+'  FROM '+@TB+@WHERE2+' ORDER BY '+@COL+' DESC'
  EXEC(@SQL)
  SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'<(SELECT MIN(pkid) '+' FROM #tmpkeylist) ORDER BY '+@COL+' DESC'
endIF @PAGE=1
  SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+CASE @ORDERBY WHEN 0 THEN '' ELSE ' DESC' ENDSET @OUTSQL = @SQLEXEC(@SQL)
drop table #tmpkeylistGO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO-----------------------------------------
调用语句:
SqlParameter[] Params = new SqlParameter[]
            {
                new SqlParameter("@TB", System.Data.SqlDbType.NVarChar, 1000), 
                new SqlParameter("@COL", System.Data.SqlDbType.NVarChar, 1000),
                new SqlParameter("@ORDERBY", System.Data.SqlDbType.Bit, 5),
                new SqlParameter("@COLLIST", System.Data.SqlDbType.NVarChar, 3000),
                new SqlParameter("@PAGESIZE", System.Data.SqlDbType.Int, 4),
                new SqlParameter("@PAGE", System.Data.SqlDbType.Int, 4),
                new SqlParameter("@CONDITION", System.Data.SqlDbType.NVarChar, 1000),
                new SqlParameter("@IfQueryCount", System.Data.SqlDbType.Bit, 1),
                new SqlParameter("@RecCount", System.Data.SqlDbType.Int, 8),
                new SqlParameter("@PAGES", System.Data.SqlDbType.Int, 8),
                new SqlParameter("@OUTSQL", System.Data.SqlDbType.NVarChar, 4000)            
            };
            Params[0].Value = TableNameList;
            Params[1].Value = KeyColumn;
            Params[2].Value = IsDescOrder;
            Params[3].Value = ColList;
            Params[4].Value = PageSize;
            Params[5].Value = PageIndex;
            Params[6].Value = Condition;
            Params[7].Value = IfQueryCount;
            Params[8].Direction = ParameterDirection.Output;
            Params[9].Direction = ParameterDirection.Output;
            Params[10].Direction = ParameterDirection.Output;
            return Params;
--------------------------------------
主要问题
1: 我要当我传入的@PAGES =0 的时候不用分页查出所有记录
2:麻烦帮我注释一下上面的存储过程 越详细越好非常感谢大家
谢谢

Answer 1

05:
select   *   from(   
        select   *,   row=row_number()   over(order   by   1)   
        from   table   
  )a   where   row   between   (页数   -1)   *   每页记录数   +   1   and   页数   *   每页记录数 

Answer 2

楼主的贴出来的变量有几个都可以不用的
2000用邹老大这个:if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_show]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_show]
GOCREATE Proc p_show
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10,   --每页的大小(行数)
@PageCurrent int=1,   --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
 ,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
 ,@Obj_ID int    --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
 ,@strjoin nvarchar(4000) --连接字段
 ,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@QueryStr)
 ,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
 ,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
 ,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end--如果显示第一页，可以直接用top来完成
if @PageCurrent=1 
begin
 select @Id1=cast(@PageSize as varchar(20))
 exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
 return
end--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
 select @Id1=cast(@PageSize as varchar(20))
  ,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20)) select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
 if @@rowcount=0   --如果表中无标识列,则检查表中是否有主键
 begin
  if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
   goto lbusetemp  --如果表中无主键,则用临时表处理  select @FdName=name from syscolumns where id=@Obj_ID and colid in(
   select colid from sysindexkeys where @Obj_ID=id and indid in(
    select indid from sysindexes where @Obj_ID=id and name in(
     select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
   )))
  if @@rowcount>1  --检查表中的主键是否为复合主键
  begin
   select @strfd='',@strjoin='',@strwhere=''
   select @strfd=@strfd+',['+name+']'
    ,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
    ,@strwhere=@strwhere+' and b.['+name+'] is null'
    from syscolumns where id=@Obj_ID and colid in(
    select colid from sysindexkeys where @Obj_ID=id and indid in(
     select indid from sysindexes where @Obj_ID=id and name in(
      select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
    )))
   select @strfd=substring(@strfd,2,2000)
    ,@strjoin=substring(@strjoin,5,4000)
    ,@strwhere=substring(@strwhere,5,4000)
   goto lbusepk
  end
 end
end
else
 goto lbusetemp/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity: 
 exec('select top '+@Id1+@FdShow+' from '+@QueryStr
  +' where '+@FdName+' not in(select top '
  +@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
  +')'+@FdOrder
  )
 return/*--表中有复合主键的处理方法--*/
lbusepk:  
 exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
  (select top 100 percent * from '+@QueryStr+@FdOrder+') a
  left join (select top '+@Id2+' '+@strfd+' 
  from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
  where '+@strwhere+') a'
  )
 return/*--用临时表处理的方法--*/
lbusetemp:  
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
 ,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
 ,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
  into #tb from'+@QueryStr+@FdOrder+'
 select '+@FdShow+' from #tb where '+@FdName+' between '
 +@Id1+' and '+@Id2
 )GO

Answer 3

下面存储过程是从网上收集,并进行了适当的修改,效率还不错.不过得配合正确的索引,才能充分发挥它的效果/*
参数说明:
1.Tables :表名称,视图
2.PrimaryKey :主关键字
3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc
4.CurrentPage :当前页码
5.PageSize :分页尺寸
6.Filter :过滤语句，不带Where 
7.Group :Group语句,不带Group ByEXEC Pagination 'CRM_Client','cusCode','LastLinkDate DESC',1,20,
'cusCode,cusName,userId,productId,levelCode,cusProvId,cusCityId,deveStatus,dredgeStat',
' cusNStatus =60  and productId = 30 ',''
***************************************************************/
ALTER     PROCEDURE Pagination
(
@Tables varchar(1000),
@PrimaryKey varchar(100),
@Sort varchar(200) = NULL,
@CurrentPage int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL,
@total INT OUTPUT
)
AS
BEGIN
/*默认排序*/
IF @Sort IS NULL or @Sort = ''
SET @Sort = @PrimaryKey
DECLARE @SortTable varchar(100)
DECLARE @SortName varchar(100)
DECLARE @strSortColumn varchar(200)
DECLARE @operator char(2)
DECLARE @type varchar(100)
DECLARE @prec int
DECLARE @totalSQL nvarchar(200) SET @totalSql= '' IF LEN(@Filter) > 0 BEGIN

SET @totalSQL= 'SELECT @TotalCount=count('+@PrimaryKey+') FROM ' + @Tables+' WHERE ' + @Filter END ELSE BEGIN SET @totalSQL= 'SELECT @TotalCount=count('+@PrimaryKey+') FROM ' + @Tables END

EXEC sp_executesql @totalSQL,N'@TotalCount int=0 output',@total output
/*设定排序语句.*/ IF CHARINDEX('DESC',@Sort)>0 BEGIN SET @strSortColumn = REPLACE(@Sort, 'DESC', '')
SET @operator = '<=' END ELSE BEGIN --IF CHARINDEX('ASC', @Sort) = 0
SET @strSortColumn = REPLACE(@Sort, 'ASC', '')
SET @operator = '>=' END IF CHARINDEX('.', @strSortColumn) > 0 BEGIN SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))
SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn)) END ELSE BEGIN
SET @SortTable = @Tables
SET @SortName = @strSortColumn
END Select @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype
Where o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0
SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(300)
DECLARE @strSimpleFilter varchar(300)
DECLARE @strGroup varchar(100) /*默认当前页*/
IF @CurrentPage < 1
SET @CurrentPage = 1
/*设置分页参数.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))
/*筛选以及分组语句.*/

IF @Filter IS NOT NULL AND @Filter != '' BEGIN SET @strFilter = ' Where ' + @Filter + ' '
SET @strSimpleFilter = ' AND ' + @Filter + ' ' END ELSE BEGIN SET @strSimpleFilter = ''
SET @strFilter = '' END
IF @Group IS NOT NULL AND @Group != ''
SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
SET @strGroup = ''
/*执行查询语句*/
EXEC(
'
DECLARE @SortColumn ' + @type + '
SET ROWCOUNT ' + @strStartRow + '
Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '
SET ROWCOUNT ' + @strPageSize + '
Select ' + @Fields + ' FROM ' + @Tables + ' Where ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' orDER BY ' + @Sort + '
'
)END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO