USP_ADDGENERALPURPOSEDATASETTABLE

CREATE procedure [dbo].[USP_ADDGENERALPURPOSEDATASETTABLE]
(
@QUEUEID uniqueidentifier,
@FIELDS nvarchar(max)
)
with execute as owner
as
begin

  declare @CREATETABLESTATEMENT nvarchar(max);
  declare @TABLENAME nvarchar(256);
  declare @RESULT bit;

  set @RESULT = 0;
  set @TABLENAME = dbo.UFN_GENERALPURPOSEEMAIL_GETMERGEDATASETTABLENAME(@QUEUEID);

  declare @TABLECHECK nvarchar(256)
  set @TABLECHECK = REPLACE(@TABLENAME, '[','');
  set @TABLECHECK = REPLACE(@TABLECHECK, ']','');
  set @TABLECHECK = REPLACE(@TABLECHECK, 'dbo.','');

  if not exists(select null from sys.objects where type = 'U' and name = @TABLECHECK)
  begin
      set @CREATETABLESTATEMENT = 'CREATE TABLE ' + @TABLENAME + '(' + @FIELDS + ') on [DEFGROUP]';
      exec(@CREATETABLESTATEMENT);

      declare @GRANTRIGHTS nvarchar(128)
      set @GRANTRIGHTS = 'grant select, insert, delete on ' + @TABLENAME + ' to BBAPPFXSERVICEROLE;'
      exec(@GRANTRIGHTS);

      set @RESULT = 1
  end

  return @RESULT

end