AspNetPager分页控件存错过程问题

[天上下雨]

我用AspNetPager分页控件，写的存储过程是这样的：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create procedure [dbo].[UP_Posting_GetList1]
(
@strtime nvarchar(100),
@startIndex int,
@endIndex int
)
as

begin
with temptbl as (
SELECT ROW_NUMBER() OVER (ORDER BY Posting_id asc)AS Row, * from Posting where convert(char(10),[Posting_time],120)= @strtime)
SELECT * FROM temptbl where Row between @startIndex and @endIndex
end

但是查询不出来数据，要是单独执行以下sql语句：
with temptbl as (
SELECT ROW_NUMBER() OVER (ORDER BY Posting_id asc)AS Row, * from Posting where convert(char(10),[Posting_time],120)= '2009-08-05')
SELECT * FROM temptbl where Row between 1 and 2
就可以查询出结果，请问一下存储过程要怎么改才能读出传进去的查询条件参数？

[天上下雨]

忘了说了，是对datalist进行分页的！

[天上下雨]

顶一下！！

[pdsnet]

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER   PROCEDURE usp_PagingLarge
@TableNames VARCHAR(200),    --表名，可以是多个表，但不能用别名
@PrimaryKey VARCHAR(100),    --主键，可以为空，但@Order为空时该值不能为空
@Fields    VARCHAR(200),        --要取出的字段，可以是多个表的字段，可以为空，为空表示select *
@PageSize INT,            --每页记录数
@CurrentPage INT,        --当前页，0表示第1页
@Filter VARCHAR(200) = '',    --条件，可以为空，不用填 where
@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
    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 @type
    DECLARE @sql NVARCHAR(4000)
 
    SET @Sql =  'DECLARE @SortColumnBegin ' + @type + '
        SET ROWCOUNT ' + Cast(@TopRows  as VARCHAR(10))+ ' SELECT @SortColumnBegin=' + 
    @SortColumn + ' FROM  ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + '
        SET ROWCOUNT ' + CAST(@PageSize AS  VARCHAR(10)) + '
        SELECT ' + @Fields + ' FROM  ' + @TableNames + ' ' + @Filter  + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + ISNULL(@Group,'') + ' ORDER BY ' + @Order + ''
 
  -- Print(@sql)
  Exec(@sql)

 
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

换这个

[天上下雨]

这个是随便哪个都可以调用的吗？

[flyerwing]

肯定与分页无关。大不了让allowpagging=true;

[天上下雨]

怎么说？不是很明白~~~

[天上下雨]

在我的那个存储过程基础上怎么改？

[qinhl99]

up

[ccll001]

跟分页控件是没有关系的

[天上下雨]

高手帮改一下这个存储过程吧！

[xray2005]

create procedure [dbo].[UP_Posting_GetList1] 
( 
@strtime nvarchar(100), 
@startIndex int, 
@endIndex int 
) 
as 

begin 

SELECT ROW_NUMBER() OVER (ORDER BY Posting_id asc)AS Row, * from Posting  where convert(char(10),[Posting_time],120)= @strtime and (Row between @startIndex and @endIndex);
end 

[天上下雨]

[Quote=引用 12 楼 xray2005 的回复:]
SQL codecreateprocedure[dbo].[UP_Posting_GetList1]
(@strtimenvarchar(100),@startIndexint,@endIndexint
)asbeginSELECT ROW_NUMBER()OVER (ORDERBY Posting_idasc)AS Row,*from Postingwhereconvert(char(10),[Posting_time],120)=@strtimeand (Rowbetween@startIndexand@endIndex);end
[/Quote]
生成存储过程时报错
“列名 'Row' 无效。”

[gongsun]

create procedure [dbo].[UP_Posting_GetList1] 
( 
@strtime nvarchar(100), 
@startIndex int, 
@endIndex int 
) 
as 

begin 
select * from ( 
SELECT ROW_NUMBER() OVER (ORDER BY Posting_id asc)AS Row, * from Posting  where convert(char(10),[Posting_time],120)= @strtime) temptbl
where temptbl.Row between @startIndex and @endIndex 
end