none
c# 存储过程分页问题, RRS feed

  • 问题

  •  

    ALTER PROCEDURE [dbo].[Select_WebNews]
     
    @B_Title nvarchar(50) ,
    @B_Pageindex int ,
    @B_Pagesize int 
      
    AS
    BEGIN

    if  @B_Pageindex=1
    BEGIN
     
      SELECT Top (@B_Pagesize) * From Web_News where B_Title=@B_Title order by B_Sort asc 
      END
    else
    BEGIN
     
     set @B_Pageindex=(@B_Pageindex-1)*@B_Pagesize;
      SELECT Top (@B_Pagesize) * From  Web_News where B_Title=@B_Title and  B_Sort>(select top (@B_Pageindex) B_Sort From  Web_News  where B_Title=@B_Title order by B_Sort asc) order by B_Sort asc 
     END
    END

     

    c# 文件报错

    Exception Details: System.Data.SqlClient.SqlException: 子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。

     

    存储过程那里错了,给我看看嘛

     

    2010年4月28日 3:55

答案

  • ALTER PROCEDURE [dbo].[Select_WebNews]
     
    @B_Title nvarchar(50) ,
    @B_Pageindex int ,
    @B_Pagesize int
     
    AS
    BEGIN
    
    if @B_Pageindex=1
    BEGIN
     
     SELECT Top (@B_Pagesize) * From Web_News where B_Title=@B_Title order by B_Sort asc
     END
    else
    BEGIN
     
     set @B_Pageindex=(@B_Pageindex-1)*@B_Pagesize;
     SELECT Top (@B_Pagesize) *
     From Web_News
     where B_Title=@B_Title and B_Sort>
     (SELECT MAX(B_Sort) FROM (select top (@B_Pageindex) B_Sort  From Web_News where B_Title=@B_Title order by B_Sort asc) a)
     order by B_Sort asc
     END
    END
    
    ALTER PROCEDURE [dbo].[Select_WebNews]
     
    @B_Title nvarchar(50) ,
    @B_Pageindex int ,
    @B_Pagesize int
     
    AS
    BEGIN
    
    if @B_Pageindex=1
    BEGIN
     
     SELECT Top (@B_Pagesize) * From Web_News where B_Title=@B_Title order by B_Sort asc
     END
    else
    BEGIN
     
     set @B_Pageindex=(@B_Pageindex-1)*@B_Pagesize;
     SELECT Top (@B_Pagesize) *
     From Web_News
     where B_Title=@B_Title and B_Sort NOT IN (select top (@B_Pageindex) B_Sort
     From Web_News where B_Title=@B_Title order by B_Sort asc) order by B_Sort asc
     END
    END
    用 NOT IN /MAX()都行

    ROY WU(吳熹)
    2010年4月29日 6:20
    版主

全部回复

  • 看不懂你的分页逻辑,

    你的错误在于,

    子查询 (select top (@B_Pageindex) B_Sort From  Web_News  where B_Title=@B_Title order by B_Sort asc) 的查询结果记录个数大于 1,不能用于 > 比较


    问题要简单,错误须详细@错误/异常/堆栈信息+操作系统+软件版本+all the context of the issue Hope Helpful | http://www.leoworks.net
    2010年4月28日 7:28
  •    SELECT Top (@B_Pagesize) * From  Web_News where B_Title=@B_Title and  B_Sort>(select top (@B_Pageindex) B_Sort From  Web_News  where B_Title=@B_Title order by B_Sort asc) order by B_Sort asc 

     

    这句怎么改呢才好

    2010年4月28日 7:44
  • ALTER PROCEDURE [dbo].[Select_WebNews]
     
    @B_Title nvarchar(50) ,
    @B_Pageindex int ,
    @B_Pagesize int
     
    AS
    BEGIN
    
    if @B_Pageindex=1
    BEGIN
     
     SELECT Top (@B_Pagesize) * From Web_News where B_Title=@B_Title order by B_Sort asc
     END
    else
    BEGIN
     
     set @B_Pageindex=(@B_Pageindex-1)*@B_Pagesize;
     SELECT Top (@B_Pagesize) *
     From Web_News
     where B_Title=@B_Title and B_Sort>
     (SELECT MAX(B_Sort) FROM (select top (@B_Pageindex) B_Sort  From Web_News where B_Title=@B_Title order by B_Sort asc) a)
     order by B_Sort asc
     END
    END
    
    ALTER PROCEDURE [dbo].[Select_WebNews]
     
    @B_Title nvarchar(50) ,
    @B_Pageindex int ,
    @B_Pagesize int
     
    AS
    BEGIN
    
    if @B_Pageindex=1
    BEGIN
     
     SELECT Top (@B_Pagesize) * From Web_News where B_Title=@B_Title order by B_Sort asc
     END
    else
    BEGIN
     
     set @B_Pageindex=(@B_Pageindex-1)*@B_Pagesize;
     SELECT Top (@B_Pagesize) *
     From Web_News
     where B_Title=@B_Title and B_Sort NOT IN (select top (@B_Pageindex) B_Sort
     From Web_News where B_Title=@B_Title order by B_Sort asc) order by B_Sort asc
     END
    END
    用 NOT IN /MAX()都行

    ROY WU(吳熹)
    2010年4月29日 6:20
    版主