USP_DECEASEDFINDERCREATEPROCESS_CREATETABLE

create procedure dbo.USP_DECEASEDFINDERCREATEPROCESS_CREATETABLE
(
  @DECEASEDFINDERID uniqueidentifier
)
with execute as owner
as
begin
  set nocount on;

  declare @TABLENAME nvarchar(128) = 'DECEASEDFINDERCREATE_' + replace(cast(@DECEASEDFINDERID 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) +
                    '  [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) +
                    ') on [DEFGROUP]';

        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 -- does exist so we need to clear the data

      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