求2表分页存储过程

Question

下面的这个就运行不了
   我需要提供思路和技巧，谢谢大家
 
Create PROCEDURE [dbo].[proc_ListPage]
(
@tblName     nvarchar(200),         ----要显示的表或多个表的连接
@fldName     nvarchar(500) = '*',     ----要显示的字段列表
@pageSize     int = 1,         ----每页显示的记录个数
@page         int = 10,         ----要显示那一页的记录
@pageCount     int = 1 output,             ----查询结果分页后的总页数
@Counts     int = 1 output,                 ----查询到的记录数
@fldSort     nvarchar(200) = null,     ----排序字段列表或条件
@Sort         bit = 1,         ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
@strCondition     nvarchar(1000) = null,     ----查询条件,不需where
@strCondition2     nvarchar(1000) = null,     ----查询条件,不需where
@ID         nvarchar(150),         ----主表的主键
@Dist                 bit = 0           ----是否添加查询字段的 DISTINCT 默认0不添加/1添加
)
AS
SET NOCOUNT ON
Declare @sqlTmp nvarchar(1000)         ----存放动态生成的SQL语句
Declare @strTmp nvarchar(1000)         ----存放取得查询结果总数的查询语句
Declare @strID     nvarchar(1000)         ----存放取得查询开头或结尾ID的查询语句Declare @strSortType nvarchar(10)     ----数据排序规则A
Declare @strFSortType nvarchar(10)     ----数据排序规则BDeclare @SqlSelect nvarchar(50)         ----对含有DISTINCT的查询进行SQL构造
Declare @SqlCounts nvarchar(50)           ----对含有DISTINCT的总数查询进行SQL构造
if @Dist   = 0
begin
     set @SqlSelect = 'select '
     set @SqlCounts = 'Count(*)'
end
else
begin
     set @SqlSelect = 'select distinct '
     set @SqlCounts = 'Count(DISTINCT '+@ID+')'
end
if @Sort=0
begin
     set @strFSortType=' ASC '
     set @strSortType=' DESC '
end
else
begin
     set @strFSortType=' DESC '
     set @strSortType=' ASC '
end--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
if @strCondition is null or @strCondition=''     --没有设置显示条件
  if @strCondition2 is null or @strCondition2=''  
begin
     set @sqlTmp =   @fldName + ' From ' + @tblName
     set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName
     set @strID = ' From ' + @tblName
end
  else
begin
     set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) and ' + @strCondition2
     set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) and ' +@strCondition2
     set @strID = ' From ' + @tblName + ' where (1>0) and  ' +@strCondition2
     set @strCondition=@strCondition2
end
else
begin
     set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) and ' + @strCondition+@strCondition2
     set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) and ' + @strCondition+@strCondition2
     set @strID = ' From ' + @tblName + ' where (1>0) and  ' + @strCondition+@strCondition2
     set @strCondition= @strCondition+@strCondition2
end----取得查询结果总数量-----
exec sp_executesql @strTmp,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
     set @tmpCounts = 1
else
     set @tmpCounts = @Counts     --取得分页总数
     set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize     /**//**当前页大于总页数 取最后一页**/
     if @page>@pageCount
         set @page=@pageCount     --/*-----数据分页2分处理-------*/
     declare @pageIndex int --总数/页大小
     declare @lastcount int --总数%页大小      set @pageIndex = @tmpCounts/@pageSize
     set @lastcount = @tmpCounts%@pageSize
     if @lastcount > 0
         set @pageIndex = @pageIndex + 1
     else
         set @lastcount = @pagesize     --//***显示分页
     if @strCondition is null or @strCondition=''     --没有设置显示条件
     begin
         if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理
             begin 
                 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                         +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
                         +' order by '+ @fldSort +' '+ @strFSortType+')'
                         +' order by '+ @fldSort +' '+ @strFSortType 
             end
         else
             begin
             set @page = @pageIndex-@page+1 --后半部分数据处理
                 if @page <= 1 --最后一页数据显示
                     set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                         +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 
                 else                
                     set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                         +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
                         +' order by '+ @fldSort +' '+ @strSortType+')'                         +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 
             end
     end     else --有查询条件
     begin
         if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2   --前半部分数据处理
         begin 
                 set @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName +' from   '+@tblName
                     +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' from '+@tblName
                     +' Where (1>0) and ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType+')'
                     +' ' + @strCondition + ' order by '+ @fldSort +' '+ @strFSortType                 
         end
         else
         begin 
             set @page = @pageIndex-@page+1 --后半部分数据处理
             if @page <= 1 --最后一页数据显示
                     set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                         +' where (1>0) and '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType
             else
                     set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName
                         +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName
                         +' where (1>0) and '+ @strCondition +' order by '+ @fldSort +' '+ @strSortType+')'
                         + @strCondition +' order by '+ @fldSort +' '+ @strSortType+') AS TempTB'+' order by '+ @fldSort +' '+ @strFSortType 
         end    
     end------返回查询结果-----
exec sp_executesql @strTmp 
print   substring(@strTmp,0,300)
print   substring(@strTmp,301,600)
print   substring(@strTmp,601,len(@strTmp))
SET NOCOUNT OFF

Answer 1

CREATE TABLE [ts_commandlog] (
[ddate] [datetime] NOT NULL ,
[dlogid] [bigint] NOT NULL ,
[dassoc_id] [bigint] NOT NULL ,
[dcommand] [varchar] (2560) COLLATE Chinese_PRC_CI_AS NULL ,
[duserid] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[dhostname] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[dtmosnode] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[dne] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[dappname] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[dappinfo] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[dres_state] [smallint] NULL 
) ON [PRIMARY]
GO
CREATE TABLE [ts_immediateresponse] (
[ddate] [datetime] NOT NULL ,
[dlogid] [bigint] NOT NULL ,
[dassoc_id] [bigint] NOT NULL ,
[dresponse] [varchar] (2560) COLLATE Chinese_PRC_CI_AS NULL ,
[dres_state] [smallint] NULL 
) ON [PRIMARY]
GO

Answer 2

IF EXISTS (SELECT name 
       FROM   sysobjects 
       WHERE  name = 'usp_PagingLarge' 
       AND    type = 'P')
    DROP PROCEDURE usp_PagingLarge
GO CREATE PROCEDURE usp_PagingLarge
@TableNames VARCHAR(200),    --表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(100),    --主键，可以为空，但@Order为空时该值不能为空
@Fields    VARCHAR(200),        --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize INT,            --每页记录数
@CurrentPage INT,        --当前页，0表示第1页
@Filter VARCHAR(3000) = '',    --条件，可以为空，不用填 where
@Filter2 varchar(3000)='',
@Group VARCHAR(200) = '',    --分组依据，可以为空，不用填 group by
@Order VARCHAR(200) = ''    --排序，可以为空，为空默认按主键升序排列，不用填 order by
AS
BEGIN
    DECLARE @SortColumn VARCHAR(200)
    DECLARE @Operator CHAR(2)
    DECLARE @SortTable VARCHAR(200)
    DECLARE @SortName VARCHAR(200)
    IF @Fields = ''
        SET @Fields = '*'
    IF @Filter = ''
        SET @Filter = 'WHERE 1=1'
    ELSE
        SET @Filter = 'WHERE ' +  @Filter+@Filter2
    IF @Group <>''
        SET @Group = 'GROUP BY ' + @Group    IF @Order <> ''
    BEGIN
        DECLARE @pos1 INT, @pos2 INT
        SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC')
        IF CHARINDEX(' DESC', @Order) > 0
            IF CHARINDEX(' ASC', @Order) > 0
            BEGIN
                IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order)
                    SET @Operator = '<'
                ELSE
                    SET @Operator = '>'
            END
            ELSE
                SET @Operator = '<'
        ELSE
            SET @Operator = '>'
        SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '')
        SET @pos1 = CHARINDEX(',', @SortColumn)
        IF @pos1 > 0
            SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1)
        SET @pos2 = CHARINDEX('.', @SortColumn)
        IF @pos2 > 0
        BEGIN
            SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1)
            IF @pos1 > 0 
                SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1)
            ELSE
                SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2)
        END
        ELSE
        BEGIN
            SET @SortTable = @TableNames
            SET @SortName = @SortColumn
        END
    END
    ELSE
    BEGIN
        SET @SortColumn = @PrimaryKey
        SET @SortTable = @TableNames
        SET @SortName = @SortColumn
        SET @Order = @SortColumn
        SET @Operator = '>'
    END    DECLARE @type varchar(50)
    DECLARE @prec int
    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 @TopRows INT
    SET @TopRows = @PageSize * @CurrentPage + 1
    print @TopRows
    print @Operator
    EXEC('
        DECLARE @SortColumnBegin  varchar(8000) 
        SET ROWCOUNT ' + @TopRows + '
        SELECT @SortColumnBegin=' + @SortColumn + ' FROM  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
        SET ROWCOUNT ' + @PageSize + '
        SELECT ' + @Fields + ' FROM  ' + @TableNames + ' ' + @Filter  + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + '    
    ')    
ENDGO
EXEC usp_PagingLarge 'bigtable inner join bigtable_author on bigtable.d_id=bigtable_author.BigTable_id', 'bigtable.d_id', 'bigtable.d_id,bigtable.d_title,bigtable.d_content,bigtable.d_time,bigtable_author.d_author',
 20, 0, '', '', 'bigtable.d_time asc,bigtable.d_id desc'

Answer 3

CREATE PROCEDURE Mypage
(
     @metabname varchar(200), --表名
     @mepagesize int, --每页显示记录数
     @mepageint   int, --分页条显示个数
     @thispage int, --当前页编号     @msrsall int output, --总记录数
     @mspagecount int output --总页数
)
AS
declare @tabsql nvarchar(200)
declare @pagecount int --分页总数
declare @rsall int --总记录数
declare @top int --提当前段数据
declare @sql varchar(200)
------------------------------------------------------------------------------------
set @tabsql='select @rsall =count(*) from '+ @metabname
exec sp_executesql @tabsql,N'@rsall int output',@rsall output
------------------------------------------------------------------------------------
if @rsall >0  --有数据
begin
set @top=@mepagesize*(@thispage-1)
set @sql='select top ' + cast(@mepagesize as varchar(100)) + ' * from '+@metabname+' where id not in(select top '+ cast(@top as varchar(100)) +' id from '+@metabname+') order by id asc'
exec(@sql)
if @rsall % @mepagesize=0
begin
set @pagecount=@rsall / @mepagesize
end
else
begin
set @pagecount=@rsall / @mepagesize+1
end
end
else --没数据
begin
set @msrsall=0
set @mspagecount=0
end
set @msrsall=@rsall  --总信息数
set @mspagecount=@pagecount --分页总数
GO

Answer 4

以 ' fd_runtime >= convert(datetime,'2007-12-02 13:29',120) and  fd_runtime<=convert(datetime,'2008-12-02 13:29',120)  and upper(fd_' 开头的 标识符 太长。最大长度为 128。
这个where太长，似乎没好的方法解决问题。