May 18, 2021 at 3:23 pm
Hi,
How can I convert the below function to a Stored procedure? Please let me know. Appreciate it. The function is causing the slowness and so I wanted to try out the SP scenario. Could you please let me know how can we convert this fn to SP.
May 18, 2021 at 3:41 pm
Considering that the above is a multi-line table value function, the syntax for creating the 2 is very similar. Just change FUNCTION to PROCEDURE, remove the RETURNS clause and you're basically there. The only other thing is don't INSERT the data into a table variable, and instead just SELECT the dataset.
Though I would also remove the NOLOCK hints, unless you really need them (which I doubt); if so why?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 18, 2021 at 3:47 pm
Great, I am almost there, quick question,
The only other thing is don't INSERT the data into a table variable, and instead just SELECT the dataset. - SHould I just comment out the INSERT part and keep the select part for each INSERT in the existing function?
Also, I need to change the table variable to the temp table in the NEW SP. So, is changing @ --># should do the trick?
May 18, 2021 at 3:47 pm
Note to OP: Please click the Insert/edit code sample button when adding code to your posts, it's really not difficult to use.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 18, 2021 at 3:49 pm
The only other thing is don't INSERT the data into a table variable, and instead just SELECT the dataset. - SHould I just comment out the INSERT part and keep the select part for each INSERT in the existing function?
I don't follow what you're asking here, if I am honest. The table variable, @TodaysAWNegBal won't exist due to the RETURNS clause being removed, so you just want to return the INSERT clause in the final statement. Though you could comment it out, it likely wouldn't make a lot of sense to anyone else reading it later.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 18, 2021 at 3:54 pm
This is what I am trying to do
Step 1 - Convert function to SP
Step 2 - Change table variable to Temp Tbl
and then I would test it out. This is just one scenario I wanted to try to see how things work out. Sorry about any confusion.
May 18, 2021 at 4:12 pm
If you want a single return set like you'd get with the function you would need to union the two queries together.
May 18, 2021 at 4:13 pm
Temp tables: instead of
declare @table table (id int)
use
create table #table (id int)
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 18, 2021 at 4:18 pm
For performance, first try converting it into an inline TVF; those get compiled directly into the code whereas a multi-line TVF does not.
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[fnGet_Example]
( @CDate DATE )
RETURNS TABLE
AS
RETURN (
WITH cte_date AS (
SELECT DATEADD(DAY, -1, @CDate) AS SDate
),
TodaysAWNegBal AS (
SELECT
Record_A_ID, Trans_Amount
FROM
dbo.tbHuge WITH (NOLOCK)
CROSS JOIN cte_date
WHERE
Trans_TransType_ID = 30
AND Trans_DateTime = SDate
AND Trans_Amount < 0
),
Tbl_LastPositiveAvailableBalance AS (
SELECT BT1.Record_A_ID, MAX(BT2.Trans_DateTime) AS Trans_DateTime
FROM TodaysAWNegBal BT1
CROSS JOIN cte_date
LEFT OUTER JOIN dbo.tbHuge BT2 WITH (NOLOCK)
ON BT1.Record_A_ID = BT2.Record_A_ID
WHERE
BT2.Trans_TransType_ID = 30
AND BT2.Trans_DateTime < SDate
AND BT2.Trans_Amount >= 0
GROUP BY BT1.Record_A_ID
)
SELECT
NAB.Record_A_ID,
NAB.Trans_Amount,
PAB.PositiveDate
FROM
TodaysAWNegBal NAB
JOIN Tbl_LastPositiveAvailableBalance PAB
ON NAB.Record_A_ID = PAB.Record_A_ID
)
/*end of function*/
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 18, 2021 at 4:58 pm
Instead of converting to a stored procedure - I would recommend changing this multi-statement table valued function to an inline-table valued function.
It also looks like there could be some options to improve the performance, but that is going to require more information about what this function does and how to determine the data to be returned.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply