execution of function with TVP

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  March 7, 2016 at 1:43 am

  #1863399

  @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