March 7, 2016 at 1:31 am
HI. I am trying to execute the following function ( it is not mine)
ALTER FUNCTION [dbo].[Dis]
(
@dis_code int,@Title_Codes Title_Code_List READONLY, reference_date date
)
RETURNS @dis_result TABLE
(
[distribution_code] [bigint] NULL,
[title_code] [numeric](10, 0) NOT NULL
)
AS
BEGIN
declare @cur_month date
declare @ref_month date
WITH Original _CTE (title_code, original_code_doc)
AS
(
select KWTIT, KWDIK from [dbo].[history]
inner join @Title_Codes on KWTIT=title_code
where ref_month = @ref_month
union
select b.KWTIT ,b.KWDIKfrom @Title_Codes a
left outer join [dbo].[history] b
on KWTIT=title_code and b.ref_month=@cur_month
left outer join [dbo].[history] c
on c.KWTIT=title_code and c.ref_month=@ref_month
where
b.KWTIT is not null and c.KWTIT is null
)
Could you tell me how can I execute the above function. How can I find the values from @Title_Codes ?
I try this select * from [dbo].[Dis] ('14230', '2015-01-28 12:06:26.990')
Is not working
Thanks in advance
March 7, 2016 at 1:43 am
@Title_Codes is a table valued parameter, so it must be treated as a table:
DECLARE @title_codes AS Title_Code_List;
-- I don't know the definition of the table type. Look it up.
INSERT INTO @title_codes VALUES ('whatever')
SELECT *
FROM [dbo].[Dis] ('14230', @title_codes, '2015-01-28 12:06:26.990');
That said, I hope the code you posted is partial and there's more happening in that function.
As it is coded now, it's a multi-statement table valued function, which is a well known performance disaster.
Moreover, there is nothing assigning a value to @cur_month and @ref_month, so they will always be = NULL.
In addition, you don't always need UNION, unless you need DISTINCT results from that set. UNION ALL will outperform in that case.
If possible, rewrite the code as follows:
ALTER FUNCTION [dbo].[Dis]
(
@dis_code int,@Title_Codes Title_Code_List READONLY, reference_date date
)
RETURNS TABLE
AS
RETURN
WITH Original _CTE (title_code, original_code_doc)
AS
(
select KWTIT, KWDIK from [dbo].[history]
inner join @Title_Codes on KWTIT=title_code
where ref_month = (expression to calculate @ref_month)
UNION ALL
select b.KWTIT ,b.KWDIK from @Title_Codes a
left outer join [dbo].[history] b
on KWTIT=title_code and b.ref_month=(expression to calculate @cur_month)
left outer join [dbo].[history] c
on c.KWTIT=title_code and c.ref_month=(expression to calculate @ref_month)
where
b.KWTIT is not null and c.KWTIT is null
)
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply