USP_COAUPDATECREATE_CREATETABLE

CREATE procedure dbo.USP_COAUPDATECREATE_CREATETABLE
(
    @COAUPDATEID uniqueidentifier
)
with execute as owner
as
begin
    set nocount on;

    declare @TABLENAME nvarchar(128) = 'COAUPDATECREATE_' + replace(cast(@COAUPDATEID as nvarchar(36)), '-', '_');
    declare @SQL nvarchar(max);

    begin try
        -- if the table to hold the output is not yet created, then create it

        if not exists(select 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @TABLENAME)
            begin
                declare @USECOMPRESSION bit = (case when serverproperty('engineedition') = 3 then 1 else 0 end);

                set @SQL = 'create table dbo.[' + @TABLENAME + '](' + char(13) +
                                        '  [ADDRESSID] uniqueidentifier not null,' + char(13) +
                                        '  [TITLE] nvarchar(100) null,' + char(13) +
                                        '  [ORGNAME] nvarchar(154) null,' + char(13) +
                                        '  [FIRSTNAME] nvarchar(50) null,' + char(13) +
                                        '  [MIDDLENAME] nvarchar(50) null,' + char(13) +
                                        '  [LASTNAME] nvarchar(100) null,' + char(13) +
                                        '  [SUFFIX] nvarchar(100) null,' + char(13) +
                                        '  [LINE1] nvarchar(50) null,' + char(13) +
                                        '  [LINE2] nvarchar(50) null,' + char(13) +
                                        '  [CITY] nvarchar(28) null,' + char(13) +
                                        '  [STATE] nvarchar(100) null,' + char(13) +
                                        '  [POSTCODE] nvarchar(12) null, ' + char(13) +
                                        '  [CONSTITUENTID] uniqueidentifier not null' + char(13) +
                                        ') on [DEFGROUP]' + char(13) +
                                     'create clustered index IX_' + @TABLENAME + '_CONSTITUENTID on dbo.[' + @TABLENAME + ']([CONSTITUENTID])' + char(13) +
                                     'create nonclustered index IX_' + @TABLENAME + '_ADDRESSID on dbo.[' + @TABLENAME + ']([ADDRESSID]) '

                if @USECOMPRESSION = 1
                    set @SQL += ' WITH (DATA_COMPRESSION = PAGE) ';

                exec (@SQL);

                /* Grant select, insert, update, and alter rights on the new table */
                set @SQL = 'grant select, insert, update, alter, delete on dbo.[' + @TABLENAME + '] to BBAPPFXSERVICEROLE ';
                exec (@SQL);

            end
                    If not exists (select 1 from [INFORMATION_SCHEMA].COLUMNS where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @TABLENAME and [COLUMN_NAME] = 'CONSTITUENTID')
            begin
                set @SQL = 'drop table dbo.[' + @TABLENAME + ']' + char(13) +
                                     'create table dbo.[' + @TABLENAME + '](' + char(13) +
                                          '  [ADDRESSID] uniqueidentifier not null,' + char(13) +
                                            '  [TITLE] nvarchar(100) null,' + char(13) +
                                            '  [ORGNAME] nvarchar(154) null,' + char(13) +
                                            '  [FIRSTNAME] nvarchar(50) null,' + char(13) +
                                            '  [MIDDLENAME] nvarchar(50) null,' + char(13) +
                                            '  [LASTNAME] nvarchar(100) null,' + char(13) +
                                            '  [SUFFIX] nvarchar(100) null,' + char(13) +
                                            '  [LINE1] nvarchar(50) null,' + char(13) +
                                            '  [LINE2] nvarchar(50) null,' + char(13) +
                                            '  [CITY] nvarchar(28) null,' + char(13) +
                                            '  [STATE] nvarchar(100) null,' + char(13) +
                                            '  [POSTCODE] nvarchar(12) null, ' + char(13) +
                                            '  [CONSTITUENTID] uniqueidentifier not null' + char(13) +
                                            ') on [DEFGROUP]' + char(13) +
                                     'create clustered index IX_' + @TABLENAME + '_CONSTITUENTID on dbo.[' + @TABLENAME + ']([CONSTITUENTID])' + char(13) +
                                     'create nonclustered index IX_' + @TABLENAME + '_ADDRESSID on dbo.[' + @TABLENAME + ']([ADDRESSID])'

                if @USECOMPRESSION = 1
                    set @SQL += ' WITH (DATA_COMPRESSION = PAGE)';

                exec (@SQL);

                /* Grant select, insert, update, and alter rights on the new table */
                set @SQL = 'grant select, insert, update, alter, delete on dbo.[' + @TABLENAME + '] to BBAPPFXSERVICEROLE';
                exec (@SQL);
        end
        else
            begin
                set @SQL = 'truncate table dbo.[' + @TABLENAME + '];';
                exec (@SQL);
            end
        end try
        begin catch
            exec dbo.USP_RAISE_ERROR;
            return 1;
        end catch

        return 0;

end