USP_PDCOMPOSITESEGMENTMAPPINGVIEW_RECREATE

CREATE procedure dbo.USP_PDCOMPOSITESEGMENTMAPPINGVIEW_RECREATE
as
BEGIN
declare @string varchar(max)
declare @string2 varchar(max)

select @string = substring((select ' UNION ' + 'SELECT '+DESCRIPTIONCOLUMNNAME+' AS LONGDESCRIPTION, ID as LONGDESCRIPTIONID, convert(uniqueidentifier,'''+ convert(char(36),PDACCOUNTTABLESAVAILABLEFORSEGMENTID )+ ''') as PDACCOUNTTABLESAVAILABLEFORSEGMENTID FROM '+TableName  + case v1.TABLENAME when 'EVENT' then ' where programid is null' else ''  end 
from (select distinct DESCRIPTIONCOLUMNNAME, TABLENAME, PDACCOUNTTABLESAVAILABLEFORSEGMENTID
    from PDCompositeSegmentTableList t1 INNER JOIN PDAccountTablesAvailableForSegment t2 on t1.PDAccountTablesAvailableForSegmentID = t2.ID
    where t2.ISCODETABLE = 1 or t2.ISCODETABLE = 2) V1 
for XML path('')),8,8000)

set @string2 = ' UNION select ''<Not Used>'' as LONGDESCRIPTION, ''99999999-9999-9999-9999-999999999999'' as LONGDESCRIPTIONID, PDACCOUNTTABLESAVAILABLEFORSEGMENTID as PDACCOUNTTABLESAVAILABLEFORSEGMENTID from  PDCOMPOSITESEGMENTTABLELIST where SEQUENCE = 1'
set @string2 =  @string2 + 'UNION select ''<Not Used>'' as LONGDESCRIPTION,''99999999-9999-9999-9999-999999999999'' as LONGDESCRIPTIONID, PDACCOUNTTABLESAVAILABLEFORSEGMENTID as PDACCOUNTTABLESAVAILABLEFORSEGMENTID from  PDCOMPOSITESEGMENTTABLELIST where SEQUENCE = 2'
set @string2 =  @string2 + 'UNION select ''<Not Used>'' as LONGDESCRIPTION,''99999999-9999-9999-9999-999999999999'' as LONGDESCRIPTIONID, PDACCOUNTTABLESAVAILABLEFORSEGMENTID as PDACCOUNTTABLESAVAILABLEFORSEGMENTID from  PDCOMPOSITESEGMENTTABLELIST where SEQUENCE = 3'
set @string2 =  @string2 + 'UNION select ''<Not Used>'' as LONGDESCRIPTION,    ''99999999-9999-9999-9999-999999999999'' as LONGDESCRIPTIONID, PDACCOUNTTABLESAVAILABLEFORSEGMENTID as PDACCOUNTTABLESAVAILABLEFORSEGMENTID from  PDCOMPOSITESEGMENTTABLELIST where SEQUENCE = 4'




if not @string is null
    exec( 'ALTER VIEW dbo.PDCOMPOSITESEGMENTMAPPINGVIEW as '+@string+@string2 )
else
    exec( 'ALTER VIEW dbo.PDCOMPOSITESEGMENTMAPPINGVIEW as select '''' as LONGDESCRIPTION, ID as LONGDESCRIPTIONID, newid() as PDACCOUNTTABLESAVAILABLEFORSEGMENTID from dbo.EVENT where ID = ''00000000-0000-0000-0000-000000000000''' )

end