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))
declare @sSql varchar(500),@sSql1 varchar(2000)
if @CategoryCode <> '0'
select @sSql=@sSql+' And CategoryCode='''+@CategoryCode+''''
select @sSql1='SELECT * From ULCustomers '
select @sSql1=@sSql1+' Where CustomerId is not null '
Are you storing the results in a variable when you are executing the SP?
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.