Subquery Error

[MikeMcKeown]

I have created the stored procedure below to create reports based on a number of months. This works fine when there is only one record inserted into the temp table. If there is more I get the following error:-

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Is it possible to do what I am trying. I know you can use cursors however I believe they should be avoided when there is possible another way.

Thanks in advance

alter procedure dbo.[Reports_Generation_SP]
as

--creating the temp table and inserting the values needed
create table #inforeporting
(
Num_Months_A int,
Num_Months_C int,
no varchar(6),
date_report datetime,
type varchar(100)
)


INSERT INTO #inforeporting
select Number_Months_Study_A, Number_Months_Study_C, s.no,
Max(Date_Report_Generated) as Date_Report_Generated , Type_Report_Generated
FROM [Status-Table] as s LEFT OUTER JOIN
Practice_Reports as P ON
s.no = P.no
Group by Number_Months_Study_A, Number_Months_Study_C, s.no,
Date_Report_Generated, Type_Report_Generated



--declaring the variables to be used in the calculations
declare @Num_Months_A as int
declare @Num_Months_C as int
declare @no as varchar(6)
declare @date_report as datetime
declare @type as varchar(50)

--setting to the values held within the table
set @Num_Months_A =(select Num_Months_A from #inforeporting where Num_Months_A>0)
set @Num_Months_C=(select Num_Months_C from #inforeporting where Num_Months_C>0)
set @no =(select no from #inforeporting )
set @date_report =(SELECT IsNull(date_report,0)FROM #inforeporting)
set @type = (SELECT IsNull(type,0)FROM #inforeporting)


--checking which practice are due a reports
if(@Num_Months_A>=3 and @Num_Months_A<6 and @type!='1')
Begin
print 'I am printing the first reports'
END
Else
if(@Num_Months_A>=6 and @Num_Months_A<9 and @type!='1' and @type!='2' )
Begin
PRINT 2
End
Else
if(@Num_Months_A>=9 and @Num_Months_A<12 and @type!='1' and @type!='2' and @type!='3')
Begin
PRINT 3
End
Else
if(@Num_Months_A >=12 and @Num_Months_A <18 and @type!='1' and @type!='2' and @type!='3' and @type!='4' )
Begin
print 'in 12 months final and 4th report'
End

Else
if (@Num_Months_A >=18 and @type!='4' )
Begin
if (DATEDIFF(month, @date_report, getdate())>=6)
begin
print 'final '
end

End

 

[dbomrrsm]

you really need to discover which subquery is giving this error - what is the full error message ? does it give a clue as to where in the SP the error is i.e. line number etc

[blue]DBomrrsm[/blue]

 

[vongrunt]

Any query like this:

set @no =(select no from #inforeporting )

... can do that. If #inforeporting has many records, which one you want to select? @no can hold only one value. Or maybe you want to process all records individually, either set-based or with cursor loop?

 

[MikeMcKeown]

I have now changed the IF statements to not be a set to a variable but an actual SELECT statements:-

select Num_Months_A from #inforeporting)>=3 and (select Num_Months_A from #inforeporting)<6 )

This is the line causing an error as the temp table has two records in it.

How can you re-iterate through the table - can this only be done with a cursor