Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    4

    Question Unanswered: Select returns more than one row

    I'm running ASA 8.02 on win2k

    I'm trying to execute a dynamically prepared SQL statements in a stored procedure that returns mutiple result sets, but I'm getting the following error:
    'Select returns more than one row'. below is a sample of my code.


    CREATE procedure DBA.sp_CustomerList(@CategoryCode varchar(10))
    as
    begin
    declare @sSql varchar(500),@sSql1 varchar(2000)
    select @sSql1=''
    select @sSql=''
    if @CategoryCode <> '0'
    select @sSql=@sSql+' And CategoryCode='''+@CategoryCode+''''

    select @sSql1='SELECT * From ULCustomers '
    select @sSql1=@sSql1+' Where CustomerId is not null '
    select @sSql1=@sSql1+@sSql

    execute (@sSql1)

    end

    Call sp_CustomerList '0'
    Last edited by smaina; 05-11-04 at 05:31.

  2. #2
    Join Date
    Nov 2002
    Posts
    207
    I was able to execute the SP. Only changes I did was in declaration ...

    CREATE procedure spCommit(@Id varchar(10))
    as
    begin
    declare @sSql varchar(255),@sSql1 varchar(255)
    select @sSql1=''
    select @sSql=''
    if @Id <> '0'
    select @sSql=@sSql+' And id = '''+@Id+''''

    select @sSql1='SELECT * From t_commit '
    select @sSql1=@sSql1+' Where id is not null '
    select @sSql1=@sSql1+@sSql

    execute (@sSql1)

    end

    executed the SP as...

    spCommit '0'

    My version is 12.0.0.7

  3. #3
    Join Date
    May 2004
    Posts
    4
    I tried to execute the SP on 8.02 but got the same error message. Is there something i'm missing in the SP?

  4. #4
    Join Date
    Nov 2002
    Posts
    207
    Yeah, we are on different products. I tested it on ASE. ;-)

  5. #5
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Are you storing the results in a variable when you are executing the SP?

    eg.,

    myVar = exec SP

    If so, then you will get the error if the SP is returning more than one row. In ASE, it will take the first result (I think), but in ASA it will throw an error. So, you have to make sure that the SP is returning ONLY one row.

  6. #6
    Join Date
    May 2004
    Posts
    4
    I'm not storing the results in a variable, I want the SP to return more than one row. is there a workaround or i have to build the sql in code rather than in the stored procedure?

  7. #7
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Where are you calling this stored procedure from?

    What happens when you just run it from dbisql?

  8. #8
    Join Date
    May 2004
    Posts
    4
    when i run it on dbisql, a pop up a appears with the error message: Select returns more than one row.

  9. #9
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    I would run the debugger or put print statements to see where exactly its failing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •