UFN_MKTSOURCECODE_SOURCECODEISVALID

CREATE function dbo.[UFN_MKTSOURCECODE_SOURCECODEISVALID]
(
  @SOURCECODEID uniqueidentifier,
  @SOURCECODE nvarchar(50)
)
returns bit
as
begin
  declare @VALID bit = 1;
  declare @SOURCECODEPARTDEFINITIONID uniqueidentifier;
  declare @ITEMTYPECODE tinyint;
  declare @LENGTH int;
  declare @DELIMLENGTH int = 0;
  declare @TOTALLENGTH int = 0;
  declare @FORMAT nvarchar(10);
  declare @CODE nvarchar(10);
  declare @START int = 1;

  --Loop through each part of the source code so that we can validate each part...

  declare PARTCURSOR cursor local fast_forward for
    select
      [MKTSOURCECODEPARTDEFINITION].[ID],
      [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE],
      [MKTSOURCECODEITEM].[LENGTH],
      (select max(len([SCPDV].[DELIM]))
       from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] as [SCPDV]
       inner join dbo.[MKTSOURCECODEVALIDPARTVALUES] as [SCVPV] on [SCVPV].[MKTSOURCECODEPARTDEFINITIONVALUESID] = [SCPDV].[ID]
       inner join dbo.[MKTSOURCECODEITEM] as [SCI] on [SCI].[ID] = [SCVPV].[MKTSOURCECODEITEMID] and [SCI].[MKTSOURCECODEPARTDEFINITIONID] = [SCPDV].[MKTSOURCECODEPARTDEFINITIONID]
       where [SCPDV].[MKTSOURCECODEPARTDEFINITIONID] = [MKTSOURCECODEPARTDEFINITION].[ID]
       and [SCI].[SOURCECODEID] = [MKTSOURCECODEITEM].[SOURCECODEID])
    from dbo.[MKTSOURCECODEITEM]
    inner join dbo.[MKTSOURCECODEPARTDEFINITION] on [MKTSOURCECODEPARTDEFINITION].[ID] = [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID]
    where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID
    order by [MKTSOURCECODEITEM].[SEQUENCE];

  open PARTCURSOR;
  fetch next from PARTCURSOR into @SOURCECODEPARTDEFINITIONID, @ITEMTYPECODE, @LENGTH, @DELIMLENGTH;

  while (@@FETCH_STATUS = 0 and @VALID = 1)
  begin
    set @TOTALLENGTH += @LENGTH + @DELIMLENGTH;
    set @CODE = substring(@SOURCECODE, @START, @LENGTH);

    set @VALID = (case when dbo.[UFN_MKTSOURCECODEPARTDEFINITIONVALUES_GETIDFROMCODE](@SOURCECODEID, @CODE, @ITEMTYPECODE, @SOURCECODEPARTDEFINITIONID) is not null then 1 else 0 end);

    set @START += @LENGTH + @DELIMLENGTH;

    fetch next from PARTCURSOR into @SOURCECODEPARTDEFINITIONID, @ITEMTYPECODE, @LENGTH, @DELIMLENGTH;
  end

  close PARTCURSOR;
  deallocate PARTCURSOR;

  if @VALID = 1 and len(@SOURCECODE) <> (@TOTALLENGTH - @DELIMLENGTH)
    set @VALID = 0;

  return @VALID;
end