USP_DATAFORMINSTANCE_CREATEORUPDATE_SP

Create or update data form default instance with a stored procedure implementation

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@FORMNAME	nvarchar(60)	IN
@DESCRIPTION	nvarchar(1000)	IN
@FORMDEFXML	xml	IN
@TEMPLATEID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SPECUINAME	nvarchar(60)	IN
@MARTKEY	nvarchar(255)	IN

Definition

Copy



CREATE procedure [dbo].[USP_DATAFORMINSTANCE_CREATEORUPDATE_SP]
    @ID uniqueidentifier = null output, 
    @FORMNAME nvarchar(60),
    @DESCRIPTION nvarchar(1000) = '',
    @FORMDEFXML xml,
    @TEMPLATEID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @SPECUINAME nvarchar(60) = N'',
  @MARTKEY nvarchar(255) = null

as

set nocount on;

if @SPECUINAME is null
    set @SPECUINAME = N'';

if @MARTKEY = ''
    set @MARTKEY = null;

declare @ExistingID uniqueidentifier;

if @ID is null
    begin
      select @ExistingID = [ID] from dbo.[DATAFORMINSTANCECATALOG] where [FORMNAME] = @FORMNAME;
      set @ID = @ExistingID;
    end
else
    select @ExistingID = [ID] from dbo.[DATAFORMINSTANCECATALOG] where [ID] = @ID;

if @CHANGEAGENTID is null  exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;


if @ExistingID is null 

    begin
        if @ID is null set @ID = NewID();

        insert into dbo.[DATAFORMINSTANCECATALOG] ([ID], [FORMNAME], [DESCRIPTION], [FORMUIXML], [DATAFORMTEMPLATECATALOGID], [ADDEDBYID], [CHANGEDBYID], [SPECUINAME], [MARTKEY])
            values (@ID, @FORMNAME, @DESCRIPTION, @FORMDEFXML, @TEMPLATEID, @CHANGEAGENTID, @CHANGEAGENTID, @SPECUINAME, @MARTKEY);

    end

else

    update dbo.[DATAFORMINSTANCECATALOG] 
        set [FORMNAME] = @FORMNAME,
            [DESCRIPTION] = @DESCRIPTION, 
            [FORMUIXML] = @FORMDEFXML,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = getdate(),
            [SPECUINAME] = @SPECUINAME,
      [MARTKEY] = @MARTKEY
        where [ID] = @ExistingID;

if @@error <> 0 return 1;

if @MARTKEY is null
  exec dbo.[USP_DATAFORMINSTANCE_CREATEORUPDATEPROCEDURES] @ID;

if @@error <> 0 return 2;

return 0;