USP_BBACONSTITUENTFILEEDUCATIONALHISTORYIMPORT
This procedure is used by the Target Analytics Constituent File Import Record Operation for saving the educational history records from a Target Analytics Constituent Import File to the database.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @EDUCATIONALINSTITUTIONNAME | nvarchar(100) | IN | |
| @EDUCATIONALHISTORYTYPECODEID | uniqueidentifier | IN | |
| @EDUCATIONALHISTORYSTATUS | nvarchar(50) | IN | |
| @CLASSOF | UDT_YEAR | IN | |
| @EDUCATIONALDEGREECODEID | uniqueidentifier | IN | |
| @GPA | decimal(3, 2) | IN | |
| @STARTDATE | UDT_FUZZYDATE | IN | |
| @ENDDATE | UDT_FUZZYDATE | IN | |
| @EDUCATIONALMAJORACODEID | uniqueidentifier | IN | |
| @EDUCATIONALMAJORBCODEID | uniqueidentifier | IN | |
| @EDUCATIONALMAJORCCODEID | uniqueidentifier | IN | |
| @EDUCATIONALMAJORDCODEID | uniqueidentifier | IN | |
| @KNOWNNAME | nvarchar(50) | IN | |
| @FRATERNITY | nvarchar(50) | IN | |
| @PREFERREDCLASSYEAR | UDT_YEAR | IN | |
| @PRIMARYALUMNI | bit | IN | |
| @EDUCATIONALMAJORADEGREETYPECODEID | uniqueidentifier | IN | |
| @EDUCATIONALMAJORBDEGREETYPECODEID | uniqueidentifier | IN | |
| @EDUCATIONALMAJORCDEGREETYPECODEID | uniqueidentifier | IN | |
| @EDUCATIONALMAJORDDEGREETYPECODEID | uniqueidentifier | IN | |
| @EDUCATIONALCOLLEGEACODEID | uniqueidentifier | IN | |
| @EDUCATIONALCOLLEGEBCODEID | uniqueidentifier | IN | |
| @EDUCATIONALCOLLEGECCODEID | uniqueidentifier | IN | |
| @EDUCATIONALCOLLEGEDCODEID | uniqueidentifier | IN | |
| @PROGRAM | nvarchar(100) | IN |
Definition
Copy
CREATE procedure dbo.USP_BBACONSTITUENTFILEEDUCATIONALHISTORYIMPORT (
@CONSTITUENTID uniqueidentifier output,
@CHANGEAGENTID uniqueidentifier,
@EDUCATIONALINSTITUTIONNAME nvarchar(100),
@EDUCATIONALHISTORYTYPECODEID uniqueidentifier = null,
@EDUCATIONALHISTORYSTATUS nvarchar(50) = '',
@CLASSOF dbo.UDT_YEAR = 0,
@EDUCATIONALDEGREECODEID uniqueidentifier = null,
@GPA decimal(3,2) = 0,
@STARTDATE dbo.UDT_FUZZYDATE = '00000000',
@ENDDATE dbo.UDT_FUZZYDATE = '00000000',
@EDUCATIONALMAJORACODEID uniqueidentifier = null,
@EDUCATIONALMAJORBCODEID uniqueidentifier = null,
@EDUCATIONALMAJORCCODEID uniqueidentifier = null,
@EDUCATIONALMAJORDCODEID uniqueidentifier = null,
@KNOWNNAME nvarchar(50) = '',
@FRATERNITY nvarchar(50) = '',
@PREFERREDCLASSYEAR dbo.UDT_YEAR = 0,
@PRIMARYALUMNI bit = 0,
@EDUCATIONALMAJORADEGREETYPECODEID uniqueidentifier = null,
@EDUCATIONALMAJORBDEGREETYPECODEID uniqueidentifier = null,
@EDUCATIONALMAJORCDEGREETYPECODEID uniqueidentifier = null,
@EDUCATIONALMAJORDDEGREETYPECODEID uniqueidentifier = null,
@EDUCATIONALCOLLEGEACODEID uniqueidentifier = null,
@EDUCATIONALCOLLEGEBCODEID uniqueidentifier = null,
@EDUCATIONALCOLLEGECCODEID uniqueidentifier = null,
@EDUCATIONALCOLLEGEDCODEID uniqueidentifier = null,
@PROGRAM nvarchar(100) = ''
) as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @ISAFFILIATED bit = 0;
declare @USEACADEMICCATALOG bit = 0;
begin try
select top 1
@USEACADEMICCATALOG = coalesce(USEACADEMICCATALOG, 0)
from
dbo.EDUCATIONALCONFIGURATION;
if len(@EDUCATIONALINSTITUTIONNAME) > 0 begin
if @CLASSOF is null or @CLASSOF > 9999 or @CLASSOF < 1753 begin
set @CLASSOF = 0;
end
declare @EDUCATIONALINSTITUTIONID uniqueidentifier;
select @EDUCATIONALINSTITUTIONID = EI.ID, @ISAFFILIATED = ISAFFILIATED from dbo.EDUCATIONALINSTITUTION EI where EI.NAME = @EDUCATIONALINSTITUTIONNAME;
if @EDUCATIONALINSTITUTIONID is null begin
set @EDUCATIONALINSTITUTIONID = newid();
exec dbo.USP_DATAFORMTEMPLATE_ADD_EDUCATIONALINSTITUTION_2
@EDUCATIONALINSTITUTIONID,
@CHANGEAGENTID,
null,
@EDUCATIONALINSTITUTIONNAME,
@ISAFFILIATED, -- ISAFFILIATED
null,
null,
null
end
declare @EDUCATIONALHISTORYID uniqueidentifier;
set @EDUCATIONALHISTORYID = newid();
if @PRIMARYALUMNI = 1 and exists (select ID from dbo.EDUCATIONALHISTORY where CONSTITUENTID = @CONSTITUENTID and ISPRIMARYRECORD = 1) begin
set @PRIMARYALUMNI = 0;
end
declare @CONSTITUENCYSTATUSCODE tinyint;
if len(@EDUCATIONALHISTORYSTATUS) > 0 begin
set @CONSTITUENCYSTATUSCODE =
case
when @EDUCATIONALHISTORYSTATUS = 'Currently Attending' then 1
when @EDUCATIONALHISTORYSTATUS = 'Incomplete' then 2
when @EDUCATIONALHISTORYSTATUS = 'Graduated' then 3
else 0
end
end
else begin
set @CONSTITUENCYSTATUSCODE = 0;
end
declare @DATEGRADUATED dbo.UDT_FUZZYDATE;
declare @DATELEFT dbo.UDT_FUZZYDATE;
set @DATEGRADUATED = '00000000'
set @DATELEFT = '00000000'
if len(@ENDDATE) > 0 begin
if @CONSTITUENCYSTATUSCODE = 3
set @DATEGRADUATED = @ENDDATE;
if @CONSTITUENCYSTATUSCODE = 2
set @DATELEFT = @ENDDATE;
end
if @PREFERREDCLASSYEAR is null or @PREFERREDCLASSYEAR > 9999 or @PREFERREDCLASSYEAR < 1753 begin
set @PREFERREDCLASSYEAR = 0;
end
/* Get program if supplied */
declare @ACADEMICCATALOGPROGRAMID uniqueidentifier;
declare @EDUCATIONALPROGRAMCODEID uniqueidentifier;
if len(@PROGRAM) > 0
begin
if (@USEACADEMICCATALOG = 1 and @ISAFFILIATED = 1)
begin
declare @ACADEMICCATALOGPROGRAM uniqueidentifier;
declare @PROGRAMCODE tinyint;
select @ACADEMICCATALOGPROGRAMID = ID from dbo.ACADEMICCATALOGPROGRAM
where EDUCATIONALINSTITUTIONID = @EDUCATIONALINSTITUTIONID and PROGRAM = @PROGRAM;
if @ACADEMICCATALOGPROGRAMID is null
begin
declare @err nvarchar(500);
set @err = 'The program of ''' + @PROGRAM + ''' is not valid for educational institution ''' + @EDUCATIONALINSTITUTIONNAME + ''''
raiserror(@err, 13, 1);
return 0;
end
end
else
begin
select @EDUCATIONALPROGRAMCODEID = ID from dbo.EDUCATIONALPROGRAMCODE where DESCRIPTION like @PROGRAM;
if @EDUCATIONALPROGRAMCODEID is null
begin
set @EDUCATIONALPROGRAMCODEID = newID();
insert into dbo.EDUCATIONALPROGRAMCODE
(ID, DESCRIPTION, ACTIVE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@EDUCATIONALPROGRAMCODEID, @PROGRAM, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
end
end
end
insert into dbo.EDUCATIONALHISTORY (
ID,
CONSTITUENTID,
EDUCATIONALINSTITUTIONID,
ISPRIMARYRECORD,
CONSTITUENCYSTATUSCODE,
ACADEMICCATALOGPROGRAMID,
EDUCATIONALPROGRAMCODEID,
DATEGRADUATED,
DATELEFT,
EDUCATIONALDEGREECODEID,
STARTDATE,
CLASSOF,
PREFERREDCLASSYEAR,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@EDUCATIONALHISTORYID,
@CONSTITUENTID,
@EDUCATIONALINSTITUTIONID,
@PRIMARYALUMNI,
@CONSTITUENCYSTATUSCODE,
@ACADEMICCATALOGPROGRAMID,
@EDUCATIONALPROGRAMCODEID,
@DATEGRADUATED,
@DATELEFT,
@EDUCATIONALDEGREECODEID,
@STARTDATE,
@CLASSOF,
@PREFERREDCLASSYEAR,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if coalesce(@EDUCATIONALMAJORACODEID, @EDUCATIONALCOLLEGEACODEID, @EDUCATIONALMAJORADEGREETYPECODEID) is not null begin
insert into dbo.EDUCATIONADDITIONALINFORMATION
(EDUCATIONALHISTORYID, EDUCATIONALCOLLEGECODEID, EDUCATIONALDEPARTMENTCODEID, EDUCATIONALDEGREETYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@EDUCATIONALHISTORYID, @EDUCATIONALCOLLEGEACODEID, @EDUCATIONALMAJORACODEID, @EDUCATIONALMAJORADEGREETYPECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end
if coalesce(@EDUCATIONALMAJORBCODEID, @EDUCATIONALCOLLEGEBCODEID, @EDUCATIONALMAJORBDEGREETYPECODEID) is not null begin
insert into dbo.EDUCATIONADDITIONALINFORMATION
(EDUCATIONALHISTORYID, EDUCATIONALCOLLEGECODEID, EDUCATIONALDEPARTMENTCODEID, EDUCATIONALDEGREETYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@EDUCATIONALHISTORYID, @EDUCATIONALCOLLEGEBCODEID, @EDUCATIONALMAJORBCODEID, @EDUCATIONALMAJORBDEGREETYPECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end
if coalesce(@EDUCATIONALMAJORCCODEID, @EDUCATIONALCOLLEGECCODEID, @EDUCATIONALMAJORCDEGREETYPECODEID) is not null begin
insert into dbo.EDUCATIONADDITIONALINFORMATION
(EDUCATIONALHISTORYID, EDUCATIONALCOLLEGECODEID, EDUCATIONALDEPARTMENTCODEID, EDUCATIONALDEGREETYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@EDUCATIONALHISTORYID, @EDUCATIONALCOLLEGECCODEID, @EDUCATIONALMAJORCCODEID, @EDUCATIONALMAJORCDEGREETYPECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end
if coalesce(@EDUCATIONALMAJORDCODEID, @EDUCATIONALCOLLEGEDCODEID, @EDUCATIONALMAJORDDEGREETYPECODEID) is not null begin
insert into dbo.EDUCATIONADDITIONALINFORMATION
(EDUCATIONALHISTORYID, EDUCATIONALCOLLEGECODEID, EDUCATIONALDEPARTMENTCODEID, EDUCATIONALDEGREETYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@EDUCATIONALHISTORYID, @EDUCATIONALCOLLEGEDCODEID, @EDUCATIONALMAJORDCODEID, @EDUCATIONALMAJORDDEGREETYPECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end
/******** Frat/Sorority to involvement ********/
if len(@FRATERNITY) > 0 begin
declare @EDUCATIONALINVOLVEMENTTYPECODEID uniqueidentifier;
if not exists (select ID from dbo.EDUCATIONALINVOLVEMENTTYPECODE where DESCRIPTION like 'Fraternity/Sorority') begin
set @EDUCATIONALINVOLVEMENTTYPECODEID = newID();
insert into dbo.EDUCATIONALINVOLVEMENTTYPECODE
(ID, DESCRIPTION, ACTIVE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(@EDUCATIONALINVOLVEMENTTYPECODEID, 'Fraternity/Sorority', 1, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
end
else
select
@EDUCATIONALINVOLVEMENTTYPECODEID = ID
from
dbo.EDUCATIONALINVOLVEMENTTYPECODE
where
DESCRIPTION like 'Fraternity/Sorority';
declare @EDUCATIONALINVOLVEMENTNAMEID uniqueidentifier;
select @EDUCATIONALINVOLVEMENTNAMEID = ID
from EDUCATIONALINVOLVEMENTNAME EIN
where EIN.NAME = @FRATERNITY and EIN.EDUCATIONALINVOLVEMENTTYPECODEID = @EDUCATIONALINVOLVEMENTTYPECODEID
if @EDUCATIONALINVOLVEMENTNAMEID is null
begin
set @EDUCATIONALINVOLVEMENTNAMEID = newid();
insert into EDUCATIONALINVOLVEMENTNAME
(
ID,
NAME,
EDUCATIONALINVOLVEMENTTYPECODEID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@EDUCATIONALINVOLVEMENTNAMEID,
@FRATERNITY,
@EDUCATIONALINVOLVEMENTTYPECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
end
insert into dbo.EDUCATIONALINVOLVEMENT (
CONSTITUENTID,
EDUCATIONALINVOLVEMENTTYPECODEID,
EDUCATIONALINVOLVEMENTNAMEID,
EDUCATIONALINSTITUTIONID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@CONSTITUENTID,
@EDUCATIONALINVOLVEMENTTYPECODEID,
@EDUCATIONALINVOLVEMENTNAMEID,
@EDUCATIONALINSTITUTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
-- convert Type, GPA and Known name to attributes
declare @ATTRIBUTECATEGORYID uniqueidentifier;
declare @TABLENAME nvarchar(150);
declare @SQL nvarchar(max);
declare @ATTRIBUTERECORDTYPEID uniqueidentifier;
declare @ATTRIBUTEDATATYPECODE int;
declare @ATTRIBUTEONLYALLOWONEPERRECORD bit;
select
@ATTRIBUTERECORDTYPEID = ID
from
dbo.ATTRIBUTERECORDTYPE
where
RECORDTYPEID = (select ID from dbo.RECORDTYPE where NAME = 'Educational History');
/******** Type ********/
if @EDUCATIONALHISTORYTYPECODEID is not null begin
set @ATTRIBUTECATEGORYID = null;
set @ATTRIBUTEDATATYPECODE = 0;
set @ATTRIBUTEONLYALLOWONEPERRECORD = 0;
if not exists (select ID from dbo.ATTRIBUTECATEGORY where NAME = 'Type' and ATTRIBUTERECORDTYPEID = @ATTRIBUTERECORDTYPEID)
exec dbo.USP_DATAFORMTEMPLATE_ADD_ATTRIBUTECATEGORY @ATTRIBUTECATEGORYID output, @CHANGEAGENTID, @ATTRIBUTERECORDTYPEID, 5, 'Type', 0, '21E9B83D-86BF-4240-BB51-112319E15FE9', null;
else begin
select
@ATTRIBUTECATEGORYID = ID
from
dbo.ATTRIBUTECATEGORY
where
NAME = 'Type' and
ATTRIBUTERECORDTYPEID = @ATTRIBUTERECORDTYPEID;
end
select
@ATTRIBUTEDATATYPECODE = AC.DATATYPECODE,
@ATTRIBUTEONLYALLOWONEPERRECORD = AC.ONLYALLOWONEPERRECORD
from
dbo.ATTRIBUTECATEGORY AC
where
ID = @ATTRIBUTECATEGORYID;
if (@ATTRIBUTEDATATYPECODE = 5) begin
set @TABLENAME = 'ATTRIBUTE' + replace(@ATTRIBUTECATEGORYID, '-', '');
if @ATTRIBUTEONLYALLOWONEPERRECORD = 0 begin
set @SQL = '
insert into dbo.' + @TABLENAME + '
(EDUCATIONALHISTORYID, EDUCATIONALHISTORYTYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (
''' + convert(nvarchar(36), @EDUCATIONALHISTORYID) + ''',
''' + convert(nvarchar(36), @EDUCATIONALHISTORYTYPECODEID) + ''',
''' + convert(nvarchar(36), @CHANGEAGENTID) + ''',
''' + convert(nvarchar(36), @CHANGEAGENTID) + ''',
''' + convert(nvarchar(50), @CURRENTDATE) + ''',
''' + convert(nvarchar(50), @CURRENTDATE) + '''
)';
exec(@SQL);
end
else begin
set @SQL = '
insert into dbo.' + @TABLENAME + '
(ID, EDUCATIONALHISTORYTYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (
''' + convert(nvarchar(36), @EDUCATIONALHISTORYID) + ''',
''' + convert(nvarchar(36), @EDUCATIONALHISTORYTYPECODEID) + ''',
''' + convert(nvarchar(36), @CHANGEAGENTID) + ''',
''' + convert(nvarchar(36), @CHANGEAGENTID) + ''',
''' + convert(nvarchar(50), @CURRENTDATE) + ''',
''' + convert(nvarchar(50), @CURRENTDATE) + '''
)';
exec(@SQL);
end
end
else begin
raiserror('An educational history attribute called "Type" already exists and it is a different datatype.', 13, 1);
return 1;
end
end
/******** GPA ********/
if @GPA is not null and @GPA > 0 begin
set @ATTRIBUTECATEGORYID = null;
set @ATTRIBUTEDATATYPECODE = 0;
set @ATTRIBUTEONLYALLOWONEPERRECORD = 0;
if not exists (select ID from dbo.ATTRIBUTECATEGORY where NAME = 'GPA' and ATTRIBUTERECORDTYPEID = @ATTRIBUTERECORDTYPEID)
exec dbo.USP_DATAFORMTEMPLATE_ADD_ATTRIBUTECATEGORY @ATTRIBUTECATEGORYID output, @CHANGEAGENTID, @ATTRIBUTERECORDTYPEID, 0, 'GPA', 0, null, null;
else
select
@ATTRIBUTECATEGORYID = ID
from
dbo.ATTRIBUTECATEGORY
where
NAME = 'GPA' and
ATTRIBUTERECORDTYPEID = @ATTRIBUTERECORDTYPEID;
select
@ATTRIBUTEDATATYPECODE = AC.DATATYPECODE,
@ATTRIBUTEONLYALLOWONEPERRECORD = AC.ONLYALLOWONEPERRECORD
from
dbo.ATTRIBUTECATEGORY AC
where
ID = @ATTRIBUTECATEGORYID;
if (@ATTRIBUTEDATATYPECODE = 0) begin
set @TABLENAME = 'ATTRIBUTE' + replace(@ATTRIBUTECATEGORYID, '-', '');
if @ATTRIBUTEONLYALLOWONEPERRECORD = 0 begin
set @SQL = '
insert into dbo.' + @TABLENAME + '
(EDUCATIONALHISTORYID, VALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (
''' + convert(nvarchar(36), @EDUCATIONALHISTORYID) + ''',
''' + convert(nvarchar(7), @GPA) + ''',
''' + convert(nvarchar(36), @CHANGEAGENTID) + ''',
''' + convert(nvarchar(36), @CHANGEAGENTID) + ''',
''' + convert(nvarchar(50), @CURRENTDATE) + ''',
''' + convert(nvarchar(50), @CURRENTDATE) + '''
)';
exec(@SQL);
end
else begin
set @SQL = '
insert into dbo.' + @TABLENAME + '
(ID, VALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (
''' + convert(nvarchar(36), @EDUCATIONALHISTORYID) + ''',
''' + convert(nvarchar(7), @GPA) + ''',
''' + convert(nvarchar(36), @CHANGEAGENTID) + ''',
''' + convert(nvarchar(36), @CHANGEAGENTID) + ''',
''' + convert(nvarchar(50), @CURRENTDATE) + ''',
''' + convert(nvarchar(50), @CURRENTDATE) + '''
)';
exec(@SQL);
end
end
else begin
raiserror('An educational history attribute called "GPA" already exists and it is a different datatype.', 13, 1);
return 1;
end
end
/******** Known name ********/
if @KNOWNNAME is not null and len(@KNOWNNAME) > 0 begin
set @ATTRIBUTECATEGORYID = null;
set @ATTRIBUTEDATATYPECODE = 0;
set @ATTRIBUTEONLYALLOWONEPERRECORD = 0;
if not exists (select ID from dbo.ATTRIBUTECATEGORY where NAME = 'Known name' and ATTRIBUTERECORDTYPEID = @ATTRIBUTERECORDTYPEID)
exec dbo.USP_DATAFORMTEMPLATE_ADD_ATTRIBUTECATEGORY @ATTRIBUTECATEGORYID output, @CHANGEAGENTID, @ATTRIBUTERECORDTYPEID, 0, 'Known name', 0, null, null;
else
select
@ATTRIBUTECATEGORYID = ID
from
dbo.ATTRIBUTECATEGORY
where
NAME = 'Known name' and
ATTRIBUTERECORDTYPEID = @ATTRIBUTERECORDTYPEID;
select
@ATTRIBUTEDATATYPECODE = AC.DATATYPECODE,
@ATTRIBUTEONLYALLOWONEPERRECORD = AC.ONLYALLOWONEPERRECORD
from
dbo.ATTRIBUTECATEGORY AC
where
ID = @ATTRIBUTECATEGORYID;
if (@ATTRIBUTEDATATYPECODE = 0) begin
set @TABLENAME = 'ATTRIBUTE' + replace(@ATTRIBUTECATEGORYID, '-', '');
if @ATTRIBUTEONLYALLOWONEPERRECORD = 0 begin
set @SQL = '
insert into dbo.' + @TABLENAME + '
(EDUCATIONALHISTORYID, VALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (
''' + convert(nvarchar(36), @EDUCATIONALHISTORYID) + ''',
''' + @KNOWNNAME + ''',
''' + convert(nvarchar(36), @CHANGEAGENTID) + ''',
''' + convert(nvarchar(36), @CHANGEAGENTID) + ''',
''' + convert(nvarchar(50), @CURRENTDATE) + ''',
''' + convert(nvarchar(50), @CURRENTDATE) + '''
)';
exec(@SQL);
end
else begin
set @SQL = '
insert into dbo.' + @TABLENAME + '
(ID, VALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (
''' + convert(nvarchar(36), @EDUCATIONALHISTORYID) + ''',
''' + @KNOWNNAME + ''',
''' + convert(nvarchar(36), @CHANGEAGENTID) + ''',
''' + convert(nvarchar(36), @CHANGEAGENTID) + ''',
''' + convert(nvarchar(50), @CURRENTDATE) + ''',
''' + convert(nvarchar(50), @CURRENTDATE) + '''
)';
exec(@SQL);
end
end
else begin
raiserror('An educational history attribute called "Known name" already exists and it is a different datatype.', 13, 1);
return 1;
end
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;