USP_BBAFILEIMPORT_ATTRIBUTES

This procedure is used by the Target Analytics File Import Record Operation for saving the attributes from a Target Analytics Import File to the database.
Parameters

Parameter	Parameter Type	Mode
@PARENTID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@TYPECODE	int	IN
@ATTRIBUTECATEGORYTABLECATALOGID	nchar	IN
@STRINGVALUE	nvarchar(max)	IN
@INTEGERVALUE	int	IN
@DATETIMEVALUE	datetime	IN
@DECIMALVALUE	money	IN
@BOOLEANVALUE	bit	IN
@STARTDATE	datetime	IN
Definition

Copy


            CREATE procedure dbo.USP_BBAFILEIMPORT_ATTRIBUTES (
                @PARENTID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @TYPECODE int = 0,
                @ATTRIBUTECATEGORYTABLECATALOGID nchar(32) = '',
                @STRINGVALUE nvarchar(max) = '',
                @INTEGERVALUE integer = 0,
                @DATETIMEVALUE datetime = null,
                @DECIMALVALUE money = 0,
                @BOOLEANVALUE bit = 0,
                @STARTDATE datetime = null
            ) as
                set nocount on;

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

                begin try
                    declare @IDCOLUMNNAME nvarchar(50);
                    declare @IDPARAMETERNAME nvarchar(50);
                    declare @VALUECOLUMNNAME nvarchar(50);
                    declare @ONLYALLOWONEPERRECORD bit;

                    select
                        @ONLYALLOWONEPERRECORD = AC.ONLYALLOWONEPERRECORD,
                        @IDCOLUMNNAME = case AC.ONLYALLOWONEPERRECORD when 0 then RT.BASETABLENAME else '' end + 'ID',
                        @VALUECOLUMNNAME = case when AC.VALUECOLUMNNAME = '' or AC.VALUECOLUMNNAME is null then 'VALUE' else AC.VALUECOLUMNNAME end,
                        @IDPARAMETERNAME = case AC.ONLYALLOWONEPERRECORD when 0 then 'PARENTID' else 'ID' end
                    from dbo.ATTRIBUTECATEGORY AC
                    left join dbo.ATTRIBUTERECORDTYPE ART on ART.ID = AC.ATTRIBUTERECORDTYPEID
                    left join dbo.RECORDTYPE RT on RT.ID = ART.RECORDTYPEID
                    where replace(AC.ID,'-','') = @ATTRIBUTECATEGORYTABLECATALOGID;

                    declare @PROCEDURESQL nvarchar(2048);
                    set @PROCEDURESQL = 'if @ONLYALLOWONEPERRECORD = 1 begin
                        declare @ATTRIBUTEID uniqueidentifier;
                        select @ATTRIBUTEID = ID from dbo.ATTRIBUTE' + @ATTRIBUTECATEGORYTABLECATALOGID + ' where ' + @IDCOLUMNNAME + ' = @PARENTID;
                        exec dbo.USP_ATTRIBUTE' + @ATTRIBUTECATEGORYTABLECATALOGID + '_DELETEBYID_WITHCHANGEAGENTID @ID = @ATTRIBUTEID, @CHANGEAGENTID = @CHANGEAGENTID;
                    end

                    if not exists(select ID from dbo.ATTRIBUTE' + @ATTRIBUTECATEGORYTABLECATALOGID + ' where ' + @IDCOLUMNNAME + ' = @PARENTID and ' + @VALUECOLUMNNAME + ' = @VALUE) begin
                        exec dbo.USP_ATTRIBUTE' + @ATTRIBUTECATEGORYTABLECATALOGID + '_ADD @' + @IDPARAMETERNAME +' = @PARENTID, @VALUE = @VALUE, @STARTDATE = @STARTDATE, @CHANGEAGENTID = @CHANGEAGENTID;
                    end';

                    declare @PARAMETERDEFINITION nvarchar(500);
                    set @PARAMETERDEFINITION = N'@PARENTID uniqueidentifier, @CHANGEAGENTID uniqueidentifier, @ONLYALLOWONEPERRECORD bit, @STARTDATE datetime';

                    if @TYPECODE = 0 begin
                        set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUE nvarchar(255)';

                        declare @VALUE0 nvarchar(255);
                        set @VALUE0 = substring(@STRINGVALUE,1,255);
                        exec sp_executesql @PROCEDURESQL, @PARAMETERDEFINITION,  @PARENTID = @PARENTID, @CHANGEAGENTID = @CHANGEAGENTID, @ONLYALLOWONEPERRECORD = @ONLYALLOWONEPERRECORD, @VALUE = @VALUE0, @STARTDATE = @STARTDATE;
                    end
                    else if @TYPECODE = 1 begin
                        set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUE int';

                        exec sp_executesql @PROCEDURESQL, @PARAMETERDEFINITION,  @PARENTID = @PARENTID, @CHANGEAGENTID = @CHANGEAGENTID, @ONLYALLOWONEPERRECORD = @ONLYALLOWONEPERRECORD, @VALUE = @INTEGERVALUE, @STARTDATE = @STARTDATE;
                    end
                    else if @TYPECODE = 2 begin
                        set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUE datetime';

                        exec sp_executesql @PROCEDURESQL, @PARAMETERDEFINITION,  @PARENTID = @PARENTID, @CHANGEAGENTID = @CHANGEAGENTID, @ONLYALLOWONEPERRECORD = @ONLYALLOWONEPERRECORD, @VALUE = @DATETIMEVALUE, @STARTDATE = @STARTDATE;
                    end
                    else if @TYPECODE = 3 begin
                        set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUE money';

                        exec sp_executesql @PROCEDURESQL, @PARAMETERDEFINITION,  @PARENTID = @PARENTID, @CHANGEAGENTID = @CHANGEAGENTID, @ONLYALLOWONEPERRECORD = @ONLYALLOWONEPERRECORD, @VALUE = @DECIMALVALUE, @STARTDATE = @STARTDATE;
                    end
                    else if @TYPECODE = 4 begin
                        set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUE bit';

                        exec sp_executesql @PROCEDURESQL, @PARAMETERDEFINITION,  @PARENTID = @PARENTID, @CHANGEAGENTID = @CHANGEAGENTID, @ONLYALLOWONEPERRECORD = @ONLYALLOWONEPERRECORD, @VALUE = @BOOLEANVALUE, @STARTDATE = @STARTDATE;
                    end
                    else if @TYPECODE = 7 begin
                        set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUE nvarchar(8)';

                        declare @VALUE7 nvarchar(8);
                        set @VALUE7 = substring(@STRINGVALUE,1,8);
                        exec sp_executesql @PROCEDURESQL, @PARAMETERDEFINITION,  @PARENTID = @PARENTID, @CHANGEAGENTID = @CHANGEAGENTID, @ONLYALLOWONEPERRECORD = @ONLYALLOWONEPERRECORD, @VALUE = @VALUE7, @STARTDATE = @STARTDATE;
                    end
                    else if @TYPECODE = 8 begin
                        set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUE nvarchar(4)';

                        declare @VALUE8 nvarchar(8);
                        set @VALUE8 = substring(@STRINGVALUE,1,4);
                        exec sp_executesql @PROCEDURESQL, @PARAMETERDEFINITION,  @PARENTID = @PARENTID, @CHANGEAGENTID = @CHANGEAGENTID, @ONLYALLOWONEPERRECORD = @ONLYALLOWONEPERRECORD, @VALUE = @VALUE8, @STARTDATE = @STARTDATE;
                    end
                    else if @TYPECODE = 9 begin
                        set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUE nvarchar(max)';

                        exec sp_executesql @PROCEDURESQL, @PARAMETERDEFINITION,  @PARENTID = @PARENTID, @CHANGEAGENTID = @CHANGEAGENTID, @ONLYALLOWONEPERRECORD = @ONLYALLOWONEPERRECORD, @VALUE = @STRINGVALUE, @STARTDATE = @STARTDATE;
                    end
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch