UFN_MKTSOURCECODE_BUILDSAMPLECODE

CREATE function dbo.[UFN_MKTSOURCECODE_BUILDSAMPLECODE]
(
  @SOURCECODEID uniqueidentifier,
  @FORM integer
)
/*
Builds the full source code for a given segment ID
*/
returns nvarchar(4000)
as begin
  declare @ITEMID uniqueidentifier;
  declare @TYPECODE tinyint;
  declare @NAME nvarchar(50);
  declare @CODE nvarchar(10);
  declare @DELIM nvarchar(1);
  declare @LENGTH tinyint;
  declare @SEQUENCE tinyint;

  declare @MAXSEQUENCE tinyint;
  select @MAXSEQUENCE = max(sequence) from dbo.[MKTSOURCECODEITEM] where SOURCECODEID = @SOURCECODEID;

  declare @SOURCECODE nvarchar(50);
  declare @SOURCECODEDEFINITION nvarchar(4000);

  set @SOURCECODE = '';
  set @SOURCECODEDEFINITION = '';

  declare PARTCURSOR cursor local fast_forward for
    select 
      [MKTSOURCECODEITEM].[ID], 
      [MKTSOURCECODEPARTDEFINITION].[ITEMTYPECODE], 
      [MKTSOURCECODEPARTDEFINITION].[NAME], 
      [MKTSOURCECODEITEM].[LENGTH],
      [MKTSOURCECODEITEM].[SEQUENCE]
    from dbo.[MKTSOURCECODEPARTDEFINITION]
      inner join dbo.[MKTSOURCECODEITEM] on [MKTSOURCECODEITEM].[MKTSOURCECODEPARTDEFINITIONID] = [MKTSOURCECODEPARTDEFINITION].[ID]
    where [MKTSOURCECODEITEM].[SOURCECODEID] = @SOURCECODEID
    order by [MKTSOURCECODEITEM].[SEQUENCE];

  open PARTCURSOR;
  fetch next from PARTCURSOR into @ITEMID, @TYPECODE, @NAME, @LENGTH, @SEQUENCE;

  while (@@FETCH_STATUS = 0)
    begin

      select
        @CODE = [MKTSOURCECODEPARTDEFINITIONVALUES].[SAMPLE],
        @DELIM = [MKTSOURCECODEPARTDEFINITIONVALUES].[DELIM] 
      from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES]
        inner join dbo.[MKTSOURCECODEVALIDPARTVALUES] on [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEPARTDEFINITIONVALUESID] = [MKTSOURCECODEPARTDEFINITIONVALUES].[ID]
      where [MKTSOURCECODEVALIDPARTVALUES].[MKTSOURCECODEITEMID] = @ITEMID

      set @SOURCECODE = @SOURCECODE + (select right(replicate('_', @LENGTH) + @CODE, @LENGTH));

      if @SEQUENCE < @MAXSEQUENCE
        set @SOURCECODE = @SOURCECODE + coalesce(@DELIM, '');

      if len(@SOURCECODEDEFINITION) > 0
        set @SOURCECODEDEFINITION = @SOURCECODEDEFINITION + ', ';

      set @SOURCECODEDEFINITION = @SOURCECODEDEFINITION + @NAME + ' (' + dbo.[UFN_MKTSOURCECODE_GETPOSITION](@ITEMID) + ')' ;

      fetch next from PARTCURSOR into @ITEMID, @TYPECODE, @NAME, @LENGTH, @SEQUENCE;
    end
  close PARTCURSOR;
  deallocate PARTCURSOR;

  return (case when @FORM = 0 then @SOURCECODE else @SOURCECODEDEFINITION end);
end