USP_DATAFORMTEMPLATE_VIEW_NAMESCHEME

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_NAMESCHEME
(
  @ID uniqueidentifier,
  @NAMESCHEMETEXT nvarchar(max) = null output,
  @NAMEPARTS xml = null output,
  @COUNTERVALUE int = null output,
  @DATALOADED bit = 0 output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select
    @DATALOADED = 1,
    @NAMESCHEMETEXT = dbo.[UFN_MKTCOMMUNICATIONNAMESCHEME_GETCURRENTNAMESCHEMETEXT](@ID),
    @NAMEPARTS = dbo.[UFN_MKTCOMMUNICATIONNAMESCHEME_GETNAMEPARTS_TOITEMLISTXML](@ID),
    @COUNTERVALUE =
    (
      select 
        isnull(case when isnull([MKTCOMMUNICATIONNAMESCHEME].[MAXCOUNTERVALUE], 0) >= [MKTCOMMUNICATIONNAMESCHEMEPART].[VALUE] then isnull([MKTCOMMUNICATIONNAMESCHEME].[MAXCOUNTERVALUE], 0) + 1
               else [MKTCOMMUNICATIONNAMESCHEMEPART].[VALUE] end, 1)
      from  
        dbo.[MKTCOMMUNICATIONNAMESCHEME]
        left join dbo.[MKTCOMMUNICATIONNAMESCHEMEPART] on [MKTCOMMUNICATIONNAMESCHEME].[ID] = [MKTCOMMUNICATIONNAMESCHEMEPART].[MKTCOMMUNICATIONNAMESCHEMEID] and [MKTCOMMUNICATIONNAMESCHEMEPART].[NAMEPARTTYPECODE] = 8
      where 
        [MKTCOMMUNICATIONNAMESCHEME].[ID] = @ID
    );

  return 0;