SQLTeam.com | Weblogs | Forums

SQL query erroring out

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.

Is dbo.spAmount_V1 a function or a stored procedure

It is the NEW Stored proc I created by converting the function. So, It is not a fn, I wanted to use the NEW Sp ( spAmount_v1) in place of function and test out the performance. I hope that info helps.

The big picture is I have a parent Sp which uses fn. The function is taking more time to run since it has table variable and accessing big tables, So I replaced the table variables inside the function to Temp table and also converted that fn to SP. Now, I just replaced the function with NEW SP name in the parent SP. since the function and SP work different while returning value, I was using EXEC dbo.spAmount_V1 (......) ...

You can't call a stored procedure like this. This is why it was a function

Thanks, then can you please help or let me know how can I call this inside parent sp?

Without knowing what dbo.spAmount_V1 does, it will be difficult to give an answer. Can you provide the details of what that does? The function will be slow since EVERY row has to call it. I would look for something that is set based