SQL Server 2005 Error

[Bill Crawley]

Hi all,

I have the following Query that I'm trying to develop to become an SP.

In the SQL pane when I do check syntax all is fine, when I go to run it however I receive the following error and I cannot see anything wrong with my statement.

Error:

Msg 116, Level 16, State 1, Line 8
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Code:
declare @LowerLimit int
declare @UpperLimit int
declare @Statement varchar(max)

set @LowerLimit = null
set @UpperLimit = 999

set @Statement = (SELECT J.jtJobID
, JT.JobType
, count(distinct I.jtInstructionID) as InstructionCount
, sum (JSOR.ActualLabourRateExternal) as SupplierLabourCosts
, sum (JSOR.ActualMaterialsRate) as ActualMaterialsRate
, sum (JSOR.ActualLabourRateExternal) + sum (JSOR.ActualLabourRateInternal) as AllLabourCosts
, sum (JSOR.ActualMaterialsRate) + sum (JSOR.ActualLabourRateExternal) + sum (JSOR.ActualLabourRateInternal) as TotalChargeable
, SL.HasVAT as HasVAT
FROM
jtJob J
left join jtCustomerSystemFieldsJob ON jtCustomerSystemFieldsJob.jtJobID = j.jtJobID
left join jtSysServiceLine SL on jtCustomerSystemFieldsJob.jtSysServiceLineID = SL.jtSysServiceLineID
left join jtJobType JT on J.jtJobTypeID = JT.jtJobTypeID
left join jtInstruction I on J.jtJobID = I.jtJobID
left join jtInstructionTask IT on I.jtInstructionID = IT.jtInstructionID
left join jtJobSORLine JSOR on J.jtJobID = JSOR.jtJobID

where
JSOR.jtSORTypeID = 2
AND JSOR.ISActual = 'Y' -- Make sure we are using actuals

group by J.jtJobID
, JT.JobType
,HasVAT
)

if (@LowerLimit is not null and @UpperLimit is not null)
begin
set @Statement = @Statement + ' Having (sum (JSOR.ActualMaterialsRate) + sum (JSOR.ActualLabourRateExternal) + sum (JSOR.ActualLabourRateInternal)) >= ' + cast(@LowerLimit as char(20))
+ ' and (sum (JSOR.ActualMaterialsRate) + sum (JSOR.ActualLabourRateExternal) + sum (JSOR.ActualLabourRateInternal)) <= ' + cast(@UpperLimit as char(20))
end
if (@LowerLimit is not null and @UpperLimit is null)
begin
set @Statement = @Statement + ' Having (sum (JSOR.ActualMaterialsRate) + sum (JSOR.ActualLabourRateExternal) + sum (JSOR.ActualLabourRateInternal)) >= ' + cast(@LowerLimit as char(20))
end
if(@LowerLimit is null and @UpperLimit is not null)
begin
set @Statement = @Statement + ' Having (sum (JSOR.ActualMaterialsRate) + sum (JSOR.ActualLabourRateExternal) + sum (JSOR.ActualLabourRateInternal)) <= ' + cast(@UpperLimit as char(20))
end

exec @Statement

[Alsvha]

Well - one of your sub-queries are returning more then one row where the statement expects one row.

It is a "run-time" error, due to your data rather then your syntax (which is why it will "parse" but not run), so it is difficult to point you in the right direction.

I'd advice you to print out your statement before executing it, then take the printed SQL and use that as foundation for debugging where the query goes wrong.