UFN_MKTSOURCECODE_GETLENGTH

CREATE function dbo.[UFN_MKTSOURCECODE_GETLENGTH]
(
  @SOURCECODEID uniqueidentifier
)
returns int
as begin

  declare @LENGTH int;

  if (select [ISHISTORICAL] from dbo.[MKTSOURCECODE] where [ID] = @SOURCECODEID) = 0
    with [SOURCECODEITEMLENGTHS] ([ITEMLENGTH]) as
    (
      select
        case when len([MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM]) > 0 
          then [MKTSOURCECODEPARTDEFINITIONVALUES].[LENGTH] + len([MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM])
          else [MKTSOURCECODEPARTDEFINITIONVALUES].[LENGTH] end as [TOTALLENGTH]
      from dbo.[MKTSOURCECODEITEM]
      inner join dbo.[MKTSOURCECODEVALIDPARTVALUES] on [MKTSOURCECODEVALIDPARTVALUES].MKTSOURCECODEITEMID = [MKTSOURCECODEITEM].[ID]
      inner join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] on [MKTSOURCECODEPARTDEFINITIONVALUES].[ID] = [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID]
      where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID
      group by [MKTSOURCECODEITEM].[SOURCECODEID], [MKTSOURCECODEITEM].[SEQUENCE], [MKTSOURCECODEPARTDEFINITIONVALUES].[LENGTH], [MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM]
    )
    select
      @LENGTH = sum([ITEMLENGTH])
    from [SOURCECODEITEMLENGTHS];
  else
    set @LENGTH = 50;

  return @LENGTH;
end