USP_BUSINESSPROCESSOUTPUT_SAVELETTERTEMPLATE

CREATE procedure dbo.[USP_BUSINESSPROCESSOUTPUT_SAVELETTERTEMPLATE]
(
  @BUSINESSPROCESSSTATUSID uniqueidentifier,
  @BUSINESSPROCESSOUTPUTTABLEKEY nvarchar(50),
  @WORDTEMPLATETABLENAME nvarchar(255),
  @WORDTEMPLATECOLUMNNAME nvarchar(255),
  @WORDTEMPLATEID uniqueidentifier,
  @LETTERTEMPLATENAME nvarchar(255),
  @CHANGEAGENTID uniqueidentifier
)
with execute as caller
as
  set nocount on;

  declare @SQL nvarchar(1024);
  declare @FILE varbinary(max);
  declare @EXISTS bit;
  declare @CURRENTDATE datetime;

  set @EXISTS = 0;
  set @SQL = 'if exists (select [TABLE_NAME] from [INFORMATION_SCHEMA].[TABLES] where [TABLE_NAME] = @TABLENAME) set @EXISTS = 1;';
  exec sp_executesql @SQL, N'@TABLENAME nvarchar(255), @EXISTS bit output', @TABLENAME = @WORDTEMPLATETABLENAME, @EXISTS = @EXISTS output;

  if @EXISTS = 0 
    begin
      raiserror(N'%s is not a valid table name.', 16, 1, @WORDTEMPLATETABLENAME);
      return 1;
    end;

  set @EXISTS = 0;
  set @SQL = 'if exists (select [COLUMN_NAME] from [INFORMATION_SCHEMA].[COLUMNS] where [TABLE_NAME] = @TABLENAME and [COLUMN_NAME] = @COLUMNNAME) set @EXISTS = 1;';
  exec sp_executesql @SQL, N'@TABLENAME nvarchar(255), @COLUMNNAME nvarchar(255), @EXISTS bit output', @TABLENAME = @WORDTEMPLATETABLENAME, @COLUMNNAME = @WORDTEMPLATECOLUMNNAME, @EXISTS = @EXISTS output;

  if @EXISTS = 0 
    begin
      raiserror(N'%s is not a valid column name.', 16, 1, @WORDTEMPLATECOLUMNNAME);
      return 1;
    end;

  set @SQL = 'select @FILE = [' + @WORDTEMPLATECOLUMNNAME + '] from dbo.[' + @WORDTEMPLATETABLENAME + '] where [ID] = @WORDTEMPLATEID;';
  exec sp_executesql @SQL, N'@WORDTEMPLATEID uniqueidentifier, @FILE varbinary(max) output', @WORDTEMPLATEID = @WORDTEMPLATEID, @FILE = @FILE output;

  set @CURRENTDATE = getdate();

  if exists (select * from dbo.[BUSINESSPROCESSOUTPUTTEMPLATE] where [BUSINESSPROCESSSTATUSID] = @BUSINESSPROCESSSTATUSID and [BUSINESSPROCESSOUTPUTTABLEKEY] = @BUSINESSPROCESSOUTPUTTABLEKEY and [WORDTEMPLATEID] = @WORDTEMPLATEID)
    update dbo.[BUSINESSPROCESSOUTPUTTEMPLATE] set 
      [LETTERTEMPLATE] = @FILE, 
      [LETTERTEMPLATENAME] = @LETTERTEMPLATENAME,
      [DATECHANGED] = @CURRENTDATE, 
      [CHANGEDBYID] = @CHANGEAGENTID
    where [BUSINESSPROCESSSTATUSID] = @BUSINESSPROCESSSTATUSID
    and [BUSINESSPROCESSOUTPUTTABLEKEY] = @BUSINESSPROCESSOUTPUTTABLEKEY
    and [WORDTEMPLATEID] = @WORDTEMPLATEID
  else
    insert into dbo.[BUSINESSPROCESSOUTPUTTEMPLATE]
    (
      [ID],
      [BUSINESSPROCESSSTATUSID],
      [BUSINESSPROCESSOUTPUTTABLEKEY],
      [WORDTEMPLATEID],
      [LETTERTEMPLATE],
      [LETTERTEMPLATENAME],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    ) values (
      newid(),
      @BUSINESSPROCESSSTATUSID,
      @BUSINESSPROCESSOUTPUTTABLEKEY,
      @WORDTEMPLATEID,
      @FILE,
      @LETTERTEMPLATENAME,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );