June 7, 2021 at 8:51 pm
Hello,
I am replacing the function in the below sp with new created Sp. I did some more modifications to the function while converting it to SP. like changing table variable to Temp table to improve performance as it is not a small table. However I am getting the below error, please let me know where I am missing or need to update
ALTER 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.AUTO 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.AUTOT2 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](@Customer_ID,@Year,@Sample_ID)
--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.
June 7, 2021 at 9:29 pm
Stored procedures return results differently from functions. Here is an example proc and how to call it:
CREATE PROCEDURE dbo.DuffProc @RetValue VARCHAR(50) OUTPUT
AS
BEGIN
SET @RetValue = 'This has been set';
END;
GO
DECLARE @RetValue VARCHAR(50);
EXEC dbo.DuffProc @RetValue = @RetValue OUTPUT;
SELECT ReturnValue = @RetValue;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 8, 2021 at 3:32 am
I tried to do the below and it is still giving error .. would you be able to modify in the above SP itself? It will help me understand better., thanks for providing the example, it does give me some idea however not sure where I need to change more.
SET @Totalval = @Totalval + Exec dbo.[spAmount_V1](@Customer_ID,@Year,@Sample_ID)
June 8, 2021 at 8:16 am
Without knowing more about dbo.[spAmount_V1], I can't do that.
Please post the 'signature' of that proc. That is, the following bit
CREATE PROCEDURE <procname> <parameters>
AS
In particular, you should have an OUTPUT parameter in the proc which gets set to the same value as dbo.[fnAmount_V1].
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 8, 2021 at 8:24 am
You have a basic syntax error calling the proc:
DECLARE @TotalValFromProc money
,@RetVal int;
EXEC @RetVal = dbo.spAmount_V1 @Customer_ID, @Year, @Sample_ID, @TotalValFromProc OUTPUT;
SET @TotalVal = @TotalVal + @TotalValFromProc;
Also you should probably have the following at the start of your procs:
SET NOCOUNT, XACT_ABORT ON;
June 8, 2021 at 6:12 pm
On a slightly different note, I would be hesitant about using the MONEY data type as it is an approximate data type. NUMERIC is likely going to be a better choice.
And one BIG question I have is why are you converting it to a stored procedure from a function? Does the function no longer work? Or is this a learning exercise?
A function and a stored procedure have 2 different purposes. Based on how you are using the function, I think a function is likely better in your scenario than a stored procedure.
Just my 2 cents though.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 8, 2021 at 6:17 pm
The function is being used in Parent SP. When I check the performance of the Parent SP, the function is taking 99% of the time.
Now, one of the things I tried before which worked out was ....converting the function to sp, buy why, here is the reason, from what I know, you cannot use temp tables inside functions, so as a workaround converting it into SP PROC and changing the table variables present inside the function to Temp Tables. After I do that, I replace the function with the converted SP PROC in the parent SP. I have seen this has improved performance when dealing with Tables more than 1TB in size. Yes, bigger tables!
Now, I could be wrong or need to learn more so please feel free to advise as well on my approcah.
June 8, 2021 at 6:18 pm
Please tell me you're not creating temp tables 1TB and larger.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 8, 2021 at 6:23 pm
No...they are just to get a few cols data and the tables in this scenario are smaller than 1TB. However, I am stuck with what to choose when dealing with functions inside SP and bigger tables. On second thoughts, let me share the function code and SP which I am working on and you can advise..
June 8, 2021 at 8:21 pm
**Function Code**
CREATE FUNCTION [Schema].[fnAuto_Amount_V1]
(
@Cust_ID INT,
@Year INT ,
@Auto_ID INT
)
RETURNS MONEY
AS
BEGIN
DECLARE @ AutoTestP MONEY
DECLARE @ AutoTestC MONEY
DECLARE @Sample_Include_For_TType_123 BIT
Declare @Exclude_For_Trans_Type_123 BIT
IF @Auto_ID = 8 OR @Auto_ID = 123
BEGIN
SET @Sample_Include_For_TType= 1
END
ELSE IF @Auto_ID = 001 OR @Auto_ID = 1234
BEGIN
SET @Exclude_For_Trans_Type = 1
END
DECLARE @tbvariable TABLE
(Sample_TAmount MONEY,
Auto_ID INT,
tA_ID INT,
Adjustment_Trans_ID INT,
Adjustment_PreviousTrans_ID INT,
Adj_Sample_TAmount MONEY,
test_Type_ID INT,
Credit_Type_ID INT)
INSERT INTO @tbvariable (Sample_TAmount,
Auto_ID,
tA_ID,
Adjustment_Trans_ID,
Adjustment_PreviousTrans_ID,
Adj_Sample_TAmount,
test_Type_ID,
Credit_Type_ID)
SELECT T.Sample_TAmount,
T.Auto_ID,
T.tA_ID,
AVA.Adjustment_Trans_ID,
AVA.Adjustment_PreviousTrans_ID,
ADJ.Sample_TAmount,
tbsampletest.TTI,
tbtc.TTI
FROM Schema.ttransT
JOIN Schema.tbAValueAdjustment AVA ON AVA.Adjustment_PreviousTrans_ID = T.Trans_ID
JOIN Schema.ttransADJ on ADJ.Trans_ID = AVA.Adjustment_Trans_ID
LEFT JOIN Schema.tbsampletest ON ADJ.Auto_ID = tbsampletest.TTI
LEFT JOIN Schema.tbtc ON tbtc.TTI = ADJ.Auto_ID
WHERE T.Trans_TransState_ID IN (2, 10)
AND T.Trans_Cleared IS NOT NULL
AND T.Trans_Year = @Year
SELECT @ SampletestAdjAmtPrevTxn= IsNull(sum(T.Adj_Sample_TAmount), 0)
FROM @tbvariable T
JOIN Schema.tbA A ON A.Account_ID = T.tA_ID
JOIN Schema.tbIAIA ON IA.IndividualAccount_Account_ID = A.Account_ID
JOIN Schema.vwI_OlderV I ON I.Individual_ID = IA.IAID
JOIN Schema.tbEIMEIM ON EIM.EIID= I.Individual_ID
WHERE EIM.Alpha_Sample_ID = @Cust_ID
AND (T.Auto_ID = @Auto_ID
OR (@Exclude_For_Trans_Type_27 IS NULL AND (@Sample_Include_For_TType_27IS NULL OR T.Auto_ID = 27)))
AND A.Account_Type_ID = 1
AND I.Individual_Test = 0
AND test_Type_ID IS NOT NULL
SELECT @ AutoTestC = IsNull(sum(T.Adj_Sample_TAmount), 0)
FROM @tbvariable T
JOIN Schema.tbA A ON A.Account_ID = T.tA_ID
JOIN Schema.tbIAIA ON IA.IndividualAccount_Account_ID = A.Account_ID
JOIN Schema.vwI_OlderV I ON I.Individual_ID = IA.IAID
JOIN Schema.tbEIMEIM ON EIM.EIID= I.Individual_ID
WHERE EIM.Alpha_Sample_ID = @Cust_ID
AND (T.Auto_ID = @Auto_ID
OR (@Exclude_For_Trans_Type_27 IS NULL AND (@Sample_Include_For_TType_27IS NULL OR T.Auto_ID = 27)))
AND A.Account_Type_ID = 1
AND I.Individual_Test = 0
AND Credit_Type_ID IS NOT NULL
-- Returning the value
RETURN isnull(@ AutoTestC,0) - isnull(@testAdjAmountWithPrevTxn,0)
END
GO
** PARENT SP**
CREATE PROCEDURE [Schema].[spAutoTest_V1]
(
@Cust_ID INT,
@Year INT,
@Auto_ID INT,
@Alpha MONEY = NULL OUTPUT
)
AS
BEGIN
SELECT
@Alpha = IsNull(SUM(T2.Auto_Amount), 0)
FROM Schema.tbEA EA WITH (NOLOCK)
INNER JOIN Schema.tbBeta T WITH (NOLOCK) on EA.EmployerAccount_Account_ID = Trans_Account_ID
INNER JOIN Schema.tbETB ETB WITH (NOLOCK) ON Auth_ID = ETB.EmployerTrans_ID
INNER JOIN Schema.tbETBMp ETBM WITH (NOLOCK) ON ETB.EmployerTransAUTO_ID = ETBM.EmployerTransAUTOMap_EmployerTransAUTO_ID
INNER JOIN Schema.tbBeta T2 WITH (NOLOCK) ON T2.Auth_ID = ETBM.EmployerTransAUTOMap_Trans_ID
WHERE EA.Alpha_Sample_ID = @Cust_ID
AND T2.Auto_ID = @Auto_ID
AND T2.TTSID = 2
AND T2.Trans_Year = @Year
SET @Alpha = @Alpha + Schema.fnAuto_Amount_V1(@Cust_ID,@Year,@Auto_ID)
END
June 8, 2021 at 8:59 pm
you sure that function is correct? you have
(@Exclude_For_Trans_Type_27 IS NULL AND (@Sample_Include_For_TType_27IS NULL OR T.Auto_ID = 27)))
but the variable isn't defined on the code you supplied.
and how is the original SP being called and how often/for how many rows - this almost seems like that the whole process should be redesigned if called often or for high volumes of records if on a loop.
function you supplied could also be changed to be a ITVF - but without changing the calling code there may be no point on it.
and it is well possible that with all those tables and view(vwI_OlderV which you didn't supply the definition of) there are missing indexes that should be addressed even before you try rewriting this.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply