CREATE PROCEDURE [dbo].[spV1]
(
@Customer_ID INT,
@Year INT,
@Sample_ID INT,
@Totalval MONEY = NULL OUTPUT
)
AS
BEGIN
SELECT @Totalval = IsNull(SUM(T2.Trans_Amount), 0)
FROM dbo.tbCustAccount EA WITH (NOLOCK)
INNER JOIN dbo.tbTrans T WITH (NOLOCK)
on EA.CustAccount_Account_ID = Trans_Account_ID
INNER JOIN dbo.tbCustTransBatch ETB WITH (NOLOCK)
ON Trans_ID = ETB.CustTrans_ID
INNER JOIN dbo.tbTBMap ETBM WITH (NOLOCK)
ON ETB.CustTransBatch_ID = ETBM.TBMap_CustTransBatch_ID
INNER JOIN dbo.tbTrans T2 WITH (NOLOCK)
ON T2.Trans_ID = ETBM.TBMap_Trans_ID
WHERE EA.CustAccount_Customer_ID = @Customer_ID
AND T2.Sample_ID = @Sample_ID
AND T2.Trans_TransState_ID = 2
AND T2.Trans_Year = @Year
SET @Totalval = @Totalval + dbo.[spAmount_V1](@Customer_ID, @Year, @Sample_ID) -- Updated line which is giving Error!
-- Before updating SET @Totalval = @Totalval + dbo.fnAmount_V1
--FnAmount_V1 - Converted to NEW SP spAmount_V1
-- Replaced Tb variable with Temp table to check if it improves performance. I cannot tell until this runs so sorry if you needed the execution time.
END
Error message: Msg 4121, Level 16, State 1, Procedure spV1, Line 26 [Batch Start Line 0]
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.spAmount_V1", or the name is ambiguous.
I tried SET @Totalval = @Totalval + Exec dbo.spAmount_V1
and did not work.