USP_SIMPLEDATALIST_CREATEORUPDATE_CLR

Create or update simple data list with a CLR implementation

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@NAME	nvarchar(60)	IN
@DESCRIPTION	nvarchar(1000)	IN
@ASSEMBLYNAME	nvarchar(128)	IN
@CLASSNAME	nvarchar(128)	IN
@PARAMETERDEFXML	xml	IN
@STATICPARAMETERDEFINITION	xml	IN
@SPECXML	xml	IN
@CHANGEAGENTID	uniqueidentifier	IN
@SPECUINAME	nvarchar(60)	IN

Definition

Copy



CREATE procedure dbo.USP_SIMPLEDATALIST_CREATEORUPDATE_CLR
    @ID uniqueidentifier = null output, 
    @NAME nvarchar(60),
    @DESCRIPTION nvarchar(1000) = '',
    @ASSEMBLYNAME nvarchar(128),
    @CLASSNAME nvarchar(128),
    @PARAMETERDEFXML xml,
    @STATICPARAMETERDEFINITION xml = null,
    @SPECXML xml,
    @CHANGEAGENTID uniqueidentifier = null,
    @SPECUINAME nvarchar(60) = N''
as

--this routine registers a CLR based simple list in the catalog


set nocount on;

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.SIMPLEDATALISTCATALOG where NAME = @NAME;
      set @ID = @ExistingID;
    end
else
    select @ExistingID = ID from dbo.SIMPLEDATALISTCATALOG where ID = @ID;


declare @FUNCID uniqueidentifier;
declare @TRANSFUNCID nvarchar(40);        
        set @TRANSFUNCID=
        @SPECXML.value(
                        '
                        declare namespace bbspec="bb_appfx_simpledatalist";
                        declare namespace c="bb_appfx_commontypes";
                        /bbspec:SimpleDataListSpec[1]/@TranslationFunctionID
                        '
                        ,'nvarchar(40)');

if (@TRANSFUNCID is not null) AND (@TRANSFUNCID <> '00000000-0000-0000-0000-000000000000')
    set @FUNCID=CAST(@TRANSFUNCID as uniqueidentifier);


if @ExistingID is null 

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


        insert into dbo.SIMPLEDATALISTCATALOG (IMPLEMENTATIONTYPE, ID, [NAME], DESCRIPTION, ASSEMBLYNAME, CLASSNAME, PARAMETERDEFXML, STATICPARAMETERDEFINITION, SIMPLEDATALISTSPECXML, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSLATIONFUNCTIONCATALOGID, SPECUINAME)
            values (1, @ID, @NAME, @DESCRIPTION, @ASSEMBLYNAME, @CLASSNAME, @PARAMETERDEFXML, @STATICPARAMETERDEFINITION, @SPECXML, @CHANGEAGENTID, @CHANGEAGENTID, getdate(), getdate(), @FUNCID, @SPECUINAME);
    end

else

    update dbo.SIMPLEDATALISTCATALOG
    set 
        IMPLEMENTATIONTYPE=1,
        [NAME] = @NAME,
        DESCRIPTION = @DESCRIPTION,
        ASSEMBLYNAME = @ASSEMBLYNAME,
        CLASSNAME = @CLASSNAME,
        PARAMETERDEFXML = @PARAMETERDEFXML,
        STATICPARAMETERDEFINITION = @STATICPARAMETERDEFINITION,
        SIMPLEDATALISTSPECXML = @SPECXML,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = getdate(),
        TRANSLATIONFUNCTIONCATALOGID = @FUNCID,
        SPECUINAME = @SPECUINAME
    where ID = @ExistingID;

if @@error <> 0 return 3;

return 0;