USP_DATALIST_CREATEORUPDATE_SP

CREATE procedure dbo.USP_DATALIST_CREATEORUPDATE_SP
    @ID uniqueidentifier = null output, 
    @NAME nvarchar(60),
    @DESCRIPTION nvarchar(1000) = '',
    @PROCEDURE nvarchar(128),
    @CONTEXTRECORDTYPE nvarchar(50) = '',
    @CONTEXTPARAMETER nvarchar(128) = '',
    @PARAMETERDEFXML xml = null,
    @RSSFEEDDEFXML xml = null,
    @OUTPUTDEFXML xml,
    @DATALISTSPECXML xml,
    @CHANGEAGENTID uniqueidentifier = null,
    @TASKREMINDERDEFINITION xml = null,
    @SPECUINAME nvarchar(60) = N'',
  @MARTKEY nvarchar(255) = null
as

--this routine registers a stored procedure based datalist in the catalog

set nocount on;

declare @RecTypeID uniqueidentifier;

declare @ExistingID uniqueidentifier;

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

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

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

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


if @CONTEXTPARAMETER is null
    set @CONTEXTPARAMETER = '';

if len(@CONTEXTPARAMETER) > 0 and @MARTKEY is null
    begin
        declare @ParamName nvarchar(128);
        select @ParamName =[name] FROM sys.parameters WHERE object_id = OBJECT_ID(@PROCEDURE) AND [name]='@' + @CONTEXTPARAMETER;

        if @ParamName is null
            begin
                raiserror ('Parameter ''%s'' is not defined for procedure ''%s''.', 16, 1, @CONTEXTPARAMETER, @PROCEDURE);
                return 2;
            end
    end


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

    exec dbo.[USP_RECORDTYPE_GETID_OR_CREATE_FROMNAME] @NAME=@CONTEXTRECORDTYPE,@CHANGEAGENTID=@CHANGEAGENTID,@RECORDTYPEID=@RecTypeID output;



declare @SECURITYUIFOLDER nvarchar(255);        
set @SECURITYUIFOLDER=
 coalesce(
        @DATALISTSPECXML.value(
                                '
                                declare namespace bbspec="bb_appfx_datalist";
                                declare namespace c="bb_appfx_commontypes";
                                /bbspec:DataListSpec[1]/@c:SecurityUIFolder
                                '
                                ,'nvarchar(255)')
        ,'');



if @ExistingID is null 

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

        insert into dbo.[DATALISTCATALOG] ([ID], [NAME], [DESCRIPTION], [PROCEDURENAME], [RECORDTYPEID], [CONTEXTPARAMETERNAME], [PARAMETERDEFINITION], [RSSFEEDDEFINITION], [OUTPUTDEFINITION], [DATALISTSPEC], [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED], [SECURITYUIFOLDER], [TASKREMINDERDEFINITION], [SPECUINAME], [MARTKEY])
            values (@ID, @NAME, @DESCRIPTION, @PROCEDURE, @RecTypeID, @CONTEXTPARAMETER, @PARAMETERDEFXML, @RSSFEEDDEFXML, @OUTPUTDEFXML, @DATALISTSPECXML, @CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate(), @SECURITYUIFOLDER, @TASKREMINDERDEFINITION, @SPECUINAME, @MARTKEY);
    end

else

    update dbo.[DATALISTCATALOG]
    set 
        [NAME] = @NAME,
        [DESCRIPTION] = @DESCRIPTION,
        [PROCEDURENAME] = @PROCEDURE,
        [ASSEMBLYNAME] = '',
        [CLASSNAME] = '',
        [IMPLEMENTATIONTYPE] = 0,
        [RECORDTYPEID] = @RecTypeID,
        [CONTEXTPARAMETERNAME] = @CONTEXTPARAMETER,
        [PARAMETERDEFINITION] = @PARAMETERDEFXML,
        [RSSFEEDDEFINITION] = @RSSFEEDDEFXML,
        [STATICPARAMETERDEFINITION] = null,
        [OUTPUTDEFINITION] = @OUTPUTDEFXML,
        [DATALISTSPEC] = @DATALISTSPECXML,
        [CHANGEDBYID] = @CHANGEAGENTID,
        [DATECHANGED] = getdate(),
        [SECURITYUIFOLDER] = @SECURITYUIFOLDER,
        [TASKREMINDERDEFINITION] = @TASKREMINDERDEFINITION,
        [SPECUINAME] = @SPECUINAME,
    [MARTKEY] = @MARTKEY
    where ID = @ExistingID;

if @@error <> 0 return 3;

return 0;