USP_CREATE_V_DIM_MARKETINGSOURCECODE

CREATE procedure [BBDW].[USP_CREATE_V_DIM_MARKETINGSOURCECODE]
  @VIEWSQL nvarchar(max)
  as
  declare @SQL nvarchar(max);

  if exists (select 1 from sys.views where name = 'v_DIM_MARKETINGSOURCECODE')
    set @SQL = 'alter view [BBDW].[v_DIM_MARKETINGSOURCECODE] as ' + char(13) + @VIEWSQL;
  else
    set @SQL = 'create view [BBDW].[v_DIM_MARKETINGSOURCECODE] as ' + char(13) + @VIEWSQL;

  execute sp_executesql @SQL;

  exec BBDW.[USP_SCHEMA_VIEW_SETVIEWCOMMENT] 'v_DIM_MARKETINGSOURCECODE','The marketing source code dimension breaks out all the marketing source code parts for marketing source codes.';
  exec BBDW.[USP_SCHEMA_VIEW_SETCOLUMNCOMMENT] 'MS_Description', 'v_DIM_MARKETINGSOURCECODE','MARKETINGSOURCECODEDIMID','BBDW.[DIM_MARKETINGSOURCECODE].[MARKETINGSOURCECODEDIMID]';
  exec BBDW.[USP_SCHEMA_VIEW_SETCOLUMNCOMMENT] 'MS_Description', 'v_DIM_MARKETINGSOURCECODE','SOURCECODELAYOUT','BBDW.[DIM_MARKETINGSOURCECODE].[MARKETINGSOURCECODEDIMID]';
  exec BBDW.[USP_SCHEMA_VIEW_SETCOLUMNCOMMENT] 'MS_Description', 'v_DIM_MARKETINGSOURCECODE','SOURCECODE','BBDW.[DIM_MARKETINGSOURCECODE].[MARKETINGSOURCECODEDIMID]';
  exec BBDW.[USP_SCHEMA_VIEW_SETCOLUMNCOMMENT] 'MS_Description', 'v_DIM_MARKETINGSOURCECODE','SOURCECODEISACTIVE','BBDW.[DIM_MARKETINGSOURCECODE].[MARKETINGSOURCECODEDIMID]';
  exec BBDW.[USP_SCHEMA_VIEW_SETCOLUMNCOMMENT] 'MS_Description', 'v_DIM_MARKETINGSOURCECODE','SITEDIMID','Reference key to the site dimension, derived using [dbo].[DIM_MARKETINGSOURCECODE].[SITEID]';

  declare @COMMENT nvarchar(max);
  declare COMMENTCURSOR cursor local fast_forward for
  select 'BBDW.[USP_SCHEMA_VIEW_SETCOLUMNCOMMENT] ''MS_Description'', ''v_DIM_MARKETINGSOURCECODE'',''' + c.[name] + ''',''BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTCODE] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = ''''' + c.[name] + ''''''';'
  from sys.columns c
  inner join sys.views v on c.[object_id] = v.[object_id]
  inner join sys.schemas s on v.[schema_id] = s.[schema_id]
  where 
    v.[name] = 'v_DIM_MARKETINGSOURCECODE'
    and c.[name] not in ('MARKETINGSOURCECODEDIMID','SOURCECODELAYOUT','SOURCECODE','SOURCECODEISACTIVE', 'SITEDIMID')
    and c.[name] not like '% Description'
    and s.[name] = 'BBDW'
  union all
  select 'BBDW.[USP_SCHEMA_VIEW_SETCOLUMNCOMMENT] ''MS_Description'', ''v_DIM_MARKETINGSOURCECODE'',''' + c.[name] + ' Description' + ''',''BBDW.[DIM_MARKETINGSOURCECODEPART].[DESCRIPTION] when BBDW.[DIM_MARKETINGSOURCECODEPART].[PARTNAME] = ''''' + c.[name] + ''''''';'
  from sys.columns c
  inner join sys.views v on c.[object_id] = v.[object_id]
  inner join sys.schemas s on v.[schema_id] = s.[schema_id]
  where
    v.[name] = 'v_DIM_MARKETINGSOURCECODE'
    and c.[name] not in ('MARKETINGSOURCECODEDIMID','SOURCECODELAYOUT','SOURCECODE','SOURCECODEISACTIVE', 'SITEDIMID')
    and c.[name] not like '% Description'
    and s.[name] = 'BBDW';

  open COMMENTCURSOR;

  fetch next from COMMENTCURSOR into @COMMENT;

  while @@fetch_status = 0
  begin
  exec sp_executesql @COMMENT;

  fetch next from COMMENTCURSOR into @COMMENT;
  end

  close COMMENTCURSOR;
  deallocate COMMENTCURSOR;