USP_PAGEDEFINITION_CREATEORUPDATE

CREATE procedure [dbo].[USP_PAGEDEFINITION_CREATEORUPDATE]
    @ID uniqueidentifier = null output,
    @NAME nvarchar(60),
    @DESCRIPTION nvarchar(1000) = '',
    @RECORDTYPE nvarchar(50) = '',
    @SPECXML xml,
    @CHANGEAGENTID uniqueidentifier = null,
    @SPECUINAME nvarchar(60) = N''
as

set nocount on;

declare @ExistingID uniqueidentifier;
declare @RecTypeID uniqueidentifier;

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

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

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

set @RecTypeID = null
if len(@RECORDTYPE) > 0 
    --Add record type if it does not exist.        

    exec dbo.USP_RECORDTYPE_GETID_OR_CREATE_FROMNAME @NAME=@RECORDTYPE,@CHANGEAGENTID=@CHANGEAGENTID,@RECORDTYPEID=@RecTypeID output;

declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();

------------------------------------------------------------------------------------------------------------------------------------------

declare @EXPRESSIONDATAFORMID uniqueidentifier;

set  @EXPRESSIONDATAFORMID =
    @SPECXML.value(
        '
        declare namespace bbspec="bb_appfx_pagedefinition";
        declare namespace c="bb_appfx_commontypes";
        /bbspec:PageDefinitionSpec[1]/@ExpressionDataFormID
        '
        ,'uniqueidentifier');

------------------------------------------------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------------------------------------------------

declare @INSTALLEDPRODUCTLISTXML xml;


with xmlnamespaces(default 'bb_appfx_pagedefinition', 'bb_appfx_commontypes' as c)
select @INSTALLEDPRODUCTLISTXML=@SPECXML.query('/PageDefinitionSpec/c:InstalledProductList');

------------------------------------------------------------------------------------------------------------------------------------------




if @ExistingID is null 

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

        insert into dbo.PAGEDEFINITIONCATALOG (ID, [NAME], DESCRIPTION, CONTEXTRECORDTYPEID, PAGEDEFINITIONSPEC, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, EXPRESSIONDATAFORMID, INSTALLEDPRODUCTLISTXML, SPECUINAME)
            values (@ID, @NAME, @DESCRIPTION, @RecTypeID, @SPECXML, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @EXPRESSIONDATAFORMID, @INSTALLEDPRODUCTLISTXML, @SPECUINAME);
    end

else

    update dbo.PAGEDEFINITIONCATALOG 
    set [NAME] = @NAME,
        DESCRIPTION = @DESCRIPTION, 
        CONTEXTRECORDTYPEID = @RecTypeID,
        PAGEDEFINITIONSPEC = @SPECXML,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE,
        EXPRESSIONDATAFORMID = @EXPRESSIONDATAFORMID,
        INSTALLEDPRODUCTLISTXML = @INSTALLEDPRODUCTLISTXML,
        SPECUINAME = @SPECUINAME
    where ID = @ExistingID;

return 0;