Thanks, that's what I got working, but what was wrong with my logic/syntax?
>Try:
>
>
>PROCEDURE [dbo].[PolicyLoansSelectTotalLoan]
> @PolicyHeaderFK uniqueidentifier,
> @TotalLoanAmount decimal(18,2) OUTPUT
>
>AS
> SET NOCOUNT ON;
> SELECT @TotalLoanAmount = cast(coalesce(SUM(LoanAmount)
> - (select SUM(AmountReceived)
> from receipts
> inner join ReceiptDetails on ReceiptFK = ReceiptPK
> where ReceiptTypeFK = 7 and receipts.policyheaderfk = @PolicyHeaderFK)
> , 0) as decimal(18,2))
> FROM [dbo].[PolicyLoans]
> WHERE [PolicyHeaderFK] = @PolicyHeaderFK
>
>
>
>
>>I have this query which apparently works:
>>
>>
>>SELECT policyheaderfk
>> ,TotalLoanAmount = cast(coalesce(SUM(LoanAmount)
>> - (select SUM(AmountReceived)
>> from receipts
>> inner join ReceiptDetails on ReceiptFK = ReceiptPK
>> where ReceiptTypeFK = 7 and receipts.policyheaderfk = policyloans.policyheaderfk)
>> , 0) as decimal(18,2))
>> FROM [dbo].[PolicyLoans]
>> group by [PolicyLoans].policyheaderfk
>>
>>I am now trying to adjust a stored procedure (which accepts a parameter to get the Loan Amount for one policy) to use similar logic:
>>
>>
ALTER PROCEDURE [dbo].[PolicyLoansSelectTotalLoan]
>> @PolicyHeaderFK uniqueidentifier,
>> @TotalLoanAmount decimal(18,2) OUTPUT
>>
>>AS
>> SET NOCOUNT ON;
>> SELECT @TotalLoanAmount = cast(coalesce(SUM(LoanAmount)
>> - (select SUM(AmountReceived)
>> from receipts
>> inner join ReceiptDetails on ReceiptFK = ReceiptPK
>> where ReceiptTypeFK = 7 and receipts.policyheaderfk = policyloans.policyheaderfk)
>> , 0) as decimal(18,2))
>> FROM [dbo].[PolicyLoans]
>> WHERE [PolicyHeaderFK] = @PolicyHeaderFK
>>
>>I get this error:
>>
>>Msg 8120, Level 16, State 1, Procedure PolicyLoansSelectTotalLoan, Line 13
>>Column 'dbo.PolicyLoans.PolicyHeaderFK' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
>>
>>Can anybody point out what I am doing wrong, please?
