USP_SEARCHLIST_CREATEORUPDATE_CLR

Used by the platform SQLCLR to register a CLR-based search list in the catalog

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@NAME	nvarchar(60)	IN
@DESCRIPTION	nvarchar(1000)	IN
@RECORDTYPE	nvarchar(50)	IN
@ASSEMBLYNAME	nvarchar(128)	IN
@CLASSNAME	nvarchar(128)	IN
@FILTERDEFXML	xml	IN
@OUTPUTDEFXML	xml	IN
@STATICPARAMETERDEFINITION	xml	IN
@SEARCHLISTSPECXML	xml	IN
@TRANSLATIONFUNCTIONCATALOGID	uniqueidentifier	IN
@QUICKFINDFIELD	nvarchar(100)	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SPECUINAME	nvarchar(60)	IN

Definition

Copy



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

--this routine registers a CLR based search in the catalog


set nocount on;

declare @RecTypeID uniqueidentifier;
declare @ExistingID uniqueidentifier;


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


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;

declare @SECURITYUIFOLDER nvarchar(255);        
set @SECURITYUIFOLDER = coalesce(@SEARCHLISTSPECXML.value(
                                '
                                declare namespace bbspec="bb_appfx_searchlist";
                                declare namespace c="bb_appfx_commontypes";
                                /bbspec: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, RECORDTYPEID, FILTERFORMDEFINITION, OUTPUTDEFINITION, STATICPARAMETERDEFINITION, SEARCHLISTSPEC, TRANSLATIONFUNCTIONCATALOGID, QUICKFINDFIELD, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SECURITYUIFOLDER, SPECUINAME)
            values (1, @ID, @NAME, @DESCRIPTION, @ASSEMBLYNAME, @CLASSNAME, @RecTypeID, @FILTERDEFXML, @OUTPUTDEFXML, @STATICPARAMETERDEFINITION, @SEARCHLISTSPECXML, @TRANSLATIONFUNCTIONCATALOGID, @QUICKFINDFIELD, @CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate(), @SECURITYUIFOLDER, @SPECUINAME);
    end

else

    update dbo.SEARCHLISTCATALOG 
    set 
        IMPLEMENTATIONTYPE=1,
        [NAME] = @NAME,
        DESCRIPTION = @DESCRIPTION,
        ASSEMBLYNAME = @ASSEMBLYNAME,
        CLASSNAME= @CLASSNAME,
        RECORDTYPEID = @RecTypeID,
        FILTERFORMDEFINITION = @FILTERDEFXML,
        OUTPUTDEFINITION = @OUTPUTDEFXML,
        STATICPARAMETERDEFINITION = @STATICPARAMETERDEFINITION,
        SEARCHLISTSPEC = @SEARCHLISTSPECXML,
        TRANSLATIONFUNCTIONCATALOGID = @TRANSLATIONFUNCTIONCATALOGID,
        QUICKFINDFIELD = @QUICKFINDFIELD,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = getdate(), 
        SECURITYUIFOLDER = @SECURITYUIFOLDER,
        SPECUINAME = @SPECUINAME
    where ID = @ExistingID

if @@error <> 0 return 3;

return 0;