USP_SEARCHLIST_CREATEORUPDATE_SP

CREATE procedure [dbo].[USP_SEARCHLIST_CREATEORUPDATE_SP]
(
    @ID uniqueidentifier = null output, 
    @NAME nvarchar(60),
    @DESCRIPTION nvarchar(1000) = '',
    @RECORDTYPE nvarchar(50),
    @PROCEDURE nvarchar(128),
    @FILTERDEFXML xml,
    @OUTPUTDEFXML xml,
    @SEARCHLISTSPECXML xml,
    @TRANSLATIONFUNCTIONID uniqueidentifier,
    @QUICKFINDFIELD nvarchar(100) = '',
    @CHANGEAGENTID uniqueidentifier = null,
    @SPECUINAME nvarchar(60) = N''
)
as

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

    set nocount on;

    declare @recTypeID uniqueidentifier;
    declare @objID int;
    declare @existingID uniqueidentifier;

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

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

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

    declare @martKey nvarchar(255);
    set @martKey = coalesce(@SEARCHLISTSPECXML.value('declare namespace tns="bb_appfx_searchlist";
                                                      /tns:SearchListSpec[1]/@MartKey
                                                      '
                                                      ,'nvarchar(255)')
                            ,'');

    if @martKey = ''
        begin
            select @objID = id from dbo.sysobjects where name = @PROCEDURE and type in ('P', 'PC');
            if @objID is null
                begin
                    raiserror ('Procedure ''%s'' does not exist or you do not have security access.', 16, 1, @PROCEDURE);
                    return 1;
                end

            declare @paramName nvarchar(128);
            select @paramName = PARAMETER_NAME 
                from INFORMATION_SCHEMA.PARAMETERS
                where (SPECIFIC_SCHEMA = 'dbo') and 
                      (SPECIFIC_NAME = @PROCEDURE) and 
                      (IS_RESULT = 'NO') and 
                      (PARAMETER_NAME = '@MAXROWS') and 
                      (DATA_TYPE = 'smallint');

            if @paramName is null
                begin
                    raiserror ('Procedure ''%s'' must have a @MAXROWS as smallint parameter defined.', 16, 1, @PROCEDURE);
                    return 2;
                end
        end

    declare @securityUIFolder nvarchar(255);
    set @securityUIFolder = coalesce(@SEARCHLISTSPECXML.value('declare namespace tns="bb_appfx_searchlist";
                                                               declare namespace c="bb_appfx_commontypes";
                                                               /tns:SearchListSpec[1]/@c:SecurityUIFolder
                                                               '
                                                               ,'nvarchar(255)')
                                    ,'');

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

    --Add record type if it does not exist.        

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

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

            insert into dbo.SEARCHLISTCATALOG (IMPLEMENTATIONTYPE, ID, [NAME], DESCRIPTION, ASSEMBLYNAME, CLASSNAME, PROCEDURENAME, RECORDTYPEID, FILTERFORMDEFINITION, OUTPUTDEFINITION, SEARCHLISTSPEC, TRANSLATIONFUNCTIONCATALOGID, QUICKFINDFIELD, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SECURITYUIFOLDER, SPECUINAME)
                values (0, @ID, @NAME, @DESCRIPTION, '', '', @PROCEDURE, @recTypeID, @FILTERDEFXML, @OUTPUTDEFXML, @SEARCHLISTSPECXML, @TRANSLATIONFUNCTIONID, @QUICKFINDFIELD, @CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate(), @securityUIFolder, @SPECUINAME)
        end

    else
        update dbo.SEARCHLISTCATALOG 
        set 
            IMPLEMENTATIONTYPE = 0,
            [NAME] = @NAME,
            DESCRIPTION = @DESCRIPTION,
            ASSEMBLYNAME = '',
            CLASSNAME = '',
            PROCEDURENAME = @PROCEDURE,
            RECORDTYPEID = @recTypeID,
            FILTERFORMDEFINITION = @FILTERDEFXML,
            OUTPUTDEFINITION = @OUTPUTDEFXML,
            STATICPARAMETERDEFINITION = null,
            SEARCHLISTSPEC = @SEARCHLISTSPECXML,
            TRANSLATIONFUNCTIONCATALOGID = @TRANSLATIONFUNCTIONID,
            QUICKFINDFIELD = @QUICKFINDFIELD,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = getdate(),
            SECURITYUIFOLDER = @securityUIFolder,
            SPECUINAME = @SPECUINAME
        where ID = @existingID

    if @@error <> 0 return 3;

return 0;