USP_GLOBALCHANGE_ADDCONSTITUENTATTRIBUTE
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @IDSETREGISTERID | uniqueidentifier | IN | |
| @ATTRIBUTECATEGORYID | uniqueidentifier | IN | |
| @STRINGVALUE | nvarchar(250) | IN | |
| @NUMBERVALUE | int | IN | |
| @MONEYVALUE | money | IN | |
| @DATEVALUE | datetime | IN | |
| @BOOLEANVALUE | bit | IN | |
| @CODETABLEVALUE | uniqueidentifier | IN | |
| @FUZZYDATEVALUE | UDT_FUZZYDATE | IN | |
| @CONSTITUENTIDVALUE | uniqueidentifier | IN | |
| @HOURMINUTEVALUE | UDT_HOURMINUTE | IN | |
| @MEMOVALUE | nvarchar(max) | IN | |
| @COMMENT | nvarchar(255) | IN | |
| @REMOVEUNQUALIFIED | bit | IN | |
| @OVERWRITEEXISTINGVALUE | bit | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @ASOF | datetime | IN | |
| @NUMBERADDED | int | INOUT | |
| @NUMBEREDITED | int | INOUT | |
| @NUMBERDELETED | int | INOUT | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @CURRENCYID | uniqueidentifier | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_GLOBALCHANGE_ADDCONSTITUENTATTRIBUTE
(
@IDSETREGISTERID uniqueidentifier = null,
@ATTRIBUTECATEGORYID uniqueidentifier,
@STRINGVALUE nvarchar(250) = null,
@NUMBERVALUE int = null,
@MONEYVALUE money = null,
@DATEVALUE datetime = null,
@BOOLEANVALUE bit = null,
@CODETABLEVALUE uniqueidentifier = null,
@FUZZYDATEVALUE udt_fuzzydate = null,
@CONSTITUENTIDVALUE uniqueidentifier = null,
@HOURMINUTEVALUE udt_hourminute = null,
@MEMOVALUE nvarchar(max) = null,
@COMMENT nvarchar(255) = null,
@REMOVEUNQUALIFIED bit,
@OVERWRITEEXISTINGVALUE bit,
@CHANGEAGENTID uniqueidentifier = null,
@ASOF as datetime = null,
@NUMBERADDED int = 0 output,
@NUMBEREDITED int = 0 output,
@NUMBERDELETED int = 0 output,
@CURRENTAPPUSERID uniqueidentifier = null,
@CURRENCYID uniqueidentifier = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null
)
as
set nocount off;
declare @DATATYPE int
declare @ATTRIBUTETABLENAME nvarchar(128)
declare @INSERTSQL nvarchar(max)
declare @UPDATESQL nvarchar(max)
declare @PARAMETERDEFINITION nvarchar(500)
declare @CURRENTDATE datetime
declare @ONEPERRECORD bit
declare @VALUECOLUMNNAME nvarchar(128)
set @NUMBERADDED = 0;
set @NUMBEREDITED = 0;
set @NUMBERDELETED = 0;
set @UPDATESQL = '';
declare @BYPASSSECURITY bit;
declare @BPID uniqueidentifier;
declare @BYPASSSITESECURITY bit;
declare @SECURITYCLAUSE nvarchar(max);
declare @MULTIPLEATTRIBUTESECURITYCLAUSE nvarchar(max);
declare @SECURITYPARAMETERS nvarchar(500);
set @BPID = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA';
set @CURRENTDATE = getdate();
if @CURRENCYID is null
set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
exec dbo.USP_SECURITY_APPUSER_BYPASSSECURITYFORBUSINESSPROCESS @CURRENTAPPUSERID, @BPID, @BYPASSSECURITY output, @BYPASSSITESECURITY output;
set @SECURITYPARAMETERS = ', @CURRENTAPPUSERIDPARAMETER uniqueidentifier, @BPIDPARAMETER uniqueidentifier, @BYPASSSECURITYPARAMETER bit, @BYPASSSITESECURITYPARAMETER bit'
set @SECURITYCLAUSE = '
left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@CURRENTAPPUSERIDPARAMETER, @BPIDPARAMETER) as CONSTIT_RACS on SELECTEDCONSTITUENT.ID = CONSTIT_RACS.ID
where
(@BYPASSSECURITYPARAMETER = 1 or CONSTIT_RACS.ID is not null)
and
(
@BYPASSSITESECURITYPARAMETER = 1 or
exists
(
select 1
from dbo.CONSTITUENT
left join dbo.CONSTITUENTSITE on CONSTITUENTSITE.CONSTITUENTID = CONSTITUENT.ID
where CONSTITUENT.ID = SELECTEDCONSTITUENT.ID
and dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERIDPARAMETER, @BPIDPARAMETER, CONSTITUENTSITE.SITEID) = 1
)
)
';
set @MULTIPLEATTRIBUTESECURITYCLAUSE = '
left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@CURRENTAPPUSERIDPARAMETER, @BPIDPARAMETER) as CONSTIT_RACS on SELECTEDCONSTITUENT.CONSTITUENTID = CONSTIT_RACS.ID
where
(@BYPASSSECURITYPARAMETER = 1 or CONSTIT_RACS.ID is not null)
and
(
@BYPASSSITESECURITYPARAMETER = 1 or
exists
(
select 1
from dbo.CONSTITUENT
left join dbo.CONSTITUENTSITE on CONSTITUENTSITE.CONSTITUENTID = CONSTITUENT.ID
where CONSTITUENT.ID = SELECTEDCONSTITUENT.CONSTITUENTID
and dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_FORSITE(@CURRENTAPPUSERIDPARAMETER, @BPIDPARAMETER, CONSTITUENTSITE.SITEID) = 1
)
)
';
if @CHANGEAGENTID is null begin
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
end
select
@DATATYPE = ATTRIBUTECATEGORY.DATATYPECODE,
@ATTRIBUTETABLENAME = TABLECATALOG.TABLENAME,
@ONEPERRECORD = ATTRIBUTECATEGORY.ONLYALLOWONEPERRECORD,
@VALUECOLUMNNAME = ATTRIBUTECATEGORY.VALUECOLUMNNAME
from
dbo.ATTRIBUTECATEGORY
inner join dbo.TABLECATALOG on TABLECATALOG.ID = ATTRIBUTECATEGORY.TABLECATALOGID
where
ATTRIBUTECATEGORY.ID = @ATTRIBUTECATEGORYID;
set @PARAMETERDEFINITION = '@COMMENTPARAMETER nvarchar(255), @CHANGEAGENTIDPARAMETER uniqueidentifier, @CURRENTDATEPARAMETER datetime, @STARTDATEPARAMETER datetime, @ENDDATEPARAMETER datetime';
if @DATATYPE = 3
set @PARAMETERDEFINITION = '@CURRENCYIDPARAMETER uniqueidentifier, ' + @PARAMETERDEFINITION;
declare @SELECTIONTABLESQL nvarchar(200);
declare @SELECTION nvarchar(43);
set @SELECTION = '';
if @IDSETREGISTERID is not null begin
declare @IDSETPARAMETERDEFINITION nvarchar(500);
set @IDSETPARAMETERDEFINITION = '@IDSETREGISTERIDPARAMETER uniqueidentifier';
select @SELECTION = '##SELECTION' + replace(cast(newid() as nvarchar(36)),'-','')
set @SELECTIONTABLESQL = 'create table ' + @SELECTION + '(ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)';
exec sp_executesql @SELECTIONTABLESQL;
declare @IDSETREGISTERSQL nvarchar(max);
--retrieve records from the selection based on whether or not the user should bypass security
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @IDSETPARAMETERDEFINITION = '@IDSETREGISTERIDPARAMETER uniqueidentifier' + @SECURITYPARAMETERS;
--insert records from the selection for which the user has rights
set @IDSETREGISTERSQL = 'insert into ' + @SELECTION + '(ID) select SELECTEDCONSTITUENT.ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERIDPARAMETER) SELECTEDCONSTITUENT ' + @SECURITYCLAUSE;
exec sp_executesql @IDSETREGISTERSQL, @IDSETPARAMETERDEFINITION, @IDSETREGISTERIDPARAMETER = @IDSETREGISTERID, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY;
end
else
begin
--insert all records from the selection
set @IDSETREGISTERSQL = 'insert into ' + @SELECTION + '(ID) select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERIDPARAMETER)';
exec sp_executesql @IDSETREGISTERSQL, @IDSETPARAMETERDEFINITION, @IDSETREGISTERIDPARAMETER = @IDSETREGISTERID;
end
--AdamBu - Bug 40526 - If the selection used is static, make sure none of the records it contains have been deleted.
if exists(
select 1
from dbo.IDSETREGISTER
where ID = @IDSETREGISTERID and STATIC = 1
)
begin
declare @STATICSQL nvarchar(max) = 'delete ' + @SELECTION + ' where ID not in(select ID from CONSTITUENT);'
exec sp_executesql @STATICSQL
end
end
/* If @REMOVEUNQUALIFIED is true and and id set is selected then delete all records from the attribute table that are not in the id set.*/
if @IDSETREGISTERID is not null and @REMOVEUNQUALIFIED = 1 begin
declare @DELETESQL nvarchar(max);
declare @DELETEPARAMETERDEFINITION nvarchar(500);
set @DELETEPARAMETERDEFINITION = '@CURRENTAPPUSERIDPARAMETER uniqueidentifier, @BPIDPARAMETER uniqueidentifier, @BYPASSSECURITYPARAMETER bit, @BYPASSSITESECURITYPARAMETER bit'
if @ONEPERRECORD = 1
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @DELETESQL = 'delete from dbo.[' + @ATTRIBUTETABLENAME + '] where ID in
(
select SELECTEDCONSTITUENT.ID
from dbo.[' + @ATTRIBUTETABLENAME + '] SELECTEDCONSTITUENT ' + @SECURITYCLAUSE +
' and SELECTEDCONSTITUENT.ID not in(select ID from ' + @SELECTION + ')
)';
end
else
begin
set @DELETESQL = 'delete from dbo.[' + @ATTRIBUTETABLENAME + '] where ID in
(
select SELECTEDCONSTITUENT.ID
from dbo.[' + @ATTRIBUTETABLENAME + '] SELECTEDCONSTITUENT ' +
' where SELECTEDCONSTITUENT.ID not in(select ID from ' + @SELECTION + ')
)';
end
else
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @DELETESQL = 'delete from dbo.[' + @ATTRIBUTETABLENAME + '] where CONSTITUENTID in
(
select SELECTEDCONSTITUENT.CONSTITUENTID
from dbo.[' + @ATTRIBUTETABLENAME + '] SELECTEDCONSTITUENT ' + @MULTIPLEATTRIBUTESECURITYCLAUSE +
' and SELECTEDCONSTITUENT.CONSTITUENTID not in(select ID from ' + @SELECTION + ')
)';
end
else
begin
set @DELETESQL = 'delete from dbo.[' + @ATTRIBUTETABLENAME + '] where CONSTITUENTID in
(
select SELECTEDCONSTITUENT.CONSTITUENTID
from dbo.[' + @ATTRIBUTETABLENAME + '] SELECTEDCONSTITUENT ' +
' where SELECTEDCONSTITUENT.CONSTITUENTID not in(select ID from ' + @SELECTION + ')
)';
end
declare @CONTEXTCACHE varbinary(128);
/* Cache current context information@ */
set @CONTEXTCACHE = CONTEXT_INFO();
/* Set CONTEXT_INFO to @CHANGEAGENTID */
set CONTEXT_INFO @CHANGEAGENTID;
/* delete records */
exec sp_executesql @DELETESQL, @DELETEPARAMETERDEFINITION, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY;
set @NUMBERDELETED = @@ROWCOUNT;
/* Reset CONTEXT_INFO to previous value */
if not @contextCache is null
set CONTEXT_INFO @CONTEXTCACHE;
end
if @IDSETREGISTERID is not null
begin
if @ONEPERRECORD = 1
begin
set @INSERTSQL = 'insert into dbo.[' + @ATTRIBUTETABLENAME + '] (ID, ' + @VALUECOLUMNNAME + ', ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + 'CURRENCYID, ';
set @INSERTSQL = @INSERTSQL + 'COMMENT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, STARTDATE, ENDDATE) select SELECTION.ID, @VALUEPARAMETER, ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + '@CURRENCYIDPARAMETER, ';
set @INSERTSQL = @INSERTSQL + '@COMMENTPARAMETER, @CHANGEAGENTIDPARAMETER, @CHANGEAGENTIDPARAMETER, @CURRENTDATEPARAMETER, @CURRENTDATEPARAMETER, @STARTDATEPARAMETER, @ENDDATEPARAMETER from ' + @SELECTION + ' as SELECTION';
/* We don't want to add a duplicate attribute value so we need to check if the value already exists before inserting the record. */
set @INSERTSQL = @INSERTSQL + ' where SELECTION.ID not in(select ID from dbo.[' + @ATTRIBUTETABLENAME + '])'
/* If overwriting the exiting value we must only insert records that don't already exist in the attribute table */
if @OVERWRITEEXISTINGVALUE = 1 begin
set @UPDATESQL = 'update dbo.[' + @ATTRIBUTETABLENAME + '] set ' + @VALUECOLUMNNAME + ' = @VALUEPARAMETER, ';
if @DATATYPE = 3
set @UPDATESQL = @UPDATESQL + 'CURRENCYID = @CURRENCYIDPARAMETER, ';
set @UPDATESQL = @UPDATESQL + 'COMMENT = @COMMENTPARAMETER, CHANGEDBYID = @CHANGEAGENTIDPARAMETER, DATECHANGED = @CURRENTDATEPARAMETER, STARTDATE = @STARTDATEPARAMETER, ENDDATE = @ENDDATEPARAMETER where [' + @ATTRIBUTETABLENAME + '].ID in (select ID from ' + @SELECTION + ')'
end
end
else
begin
set @INSERTSQL = 'insert into dbo.[' + @ATTRIBUTETABLENAME + '] (ID, CONSTITUENTID, ' + @VALUECOLUMNNAME + ', ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + 'CURRENCYID, ';
set @INSERTSQL = @INSERTSQL + 'COMMENT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, STARTDATE, ENDDATE) select newid(), SELECTION.ID, @VALUEPARAMETER, ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + '@CURRENCYIDPARAMETER, ';
set @INSERTSQL = @INSERTSQL + '@COMMENTPARAMETER, @CHANGEAGENTIDPARAMETER, @CHANGEAGENTIDPARAMETER, @CURRENTDATEPARAMETER, @CURRENTDATEPARAMETER, @STARTDATEPARAMETER, @ENDDATEPARAMETER from ' + @SELECTION + ' as SELECTION';
/* If overwriting the exiting value we must only insert records that don't already exist in the attribute table */
if @OVERWRITEEXISTINGVALUE = 1 begin
/* Don't insert new values for entries that will be overwritten */
set @INSERTSQL = @INSERTSQL + ' where SELECTION.ID not in(select CONSTITUENTID from dbo.[' + @ATTRIBUTETABLENAME + '])'
set @UPDATESQL = 'update dbo.[' + @ATTRIBUTETABLENAME + '] set ' + @VALUECOLUMNNAME + ' = @VALUEPARAMETER, ';
if @DATATYPE = 3
set @UPDATESQL = @UPDATESQL + 'CURRENCYID = @CURRENCYIDPARAMETER, ';
set @UPDATESQL = @UPDATESQL + 'COMMENT = @COMMENTPARAMETER, CHANGEDBYID = @CHANGEAGENTIDPARAMETER, DATECHANGED = @CURRENTDATEPARAMETER, STARTDATE = @STARTDATEPARAMETER, ENDDATE = @ENDDATEPARAMETER where [' + @ATTRIBUTETABLENAME + '].CONSTITUENTID in (select ID from ' + @SELECTION + ')'
end
end
end
else
begin
if @ONEPERRECORD = 1
begin
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @INSERTSQL = 'insert into dbo.[' + @ATTRIBUTETABLENAME + '] (ID, ' + @VALUECOLUMNNAME + ', ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + 'CURRENCYID, ';
set @INSERTSQL = @INSERTSQL + 'COMMENT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, STARTDATE, ENDDATE) select SELECTEDCONSTITUENT.ID, @VALUEPARAMETER, ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + '@CURRENCYIDPARAMETER, ';
set @INSERTSQL = @INSERTSQL + '@COMMENTPARAMETER, @CHANGEAGENTIDPARAMETER, @CHANGEAGENTIDPARAMETER, @CURRENTDATEPARAMETER, @CURRENTDATEPARAMETER, @STARTDATEPARAMETER, @ENDDATEPARAMETER from dbo.CONSTITUENT SELECTEDCONSTITUENT ' + @SECURITYCLAUSE;
/* We don't want to add a duplicate attribute value so we need to check if the value already exists before inserting the record. */
set @INSERTSQL = @INSERTSQL + ' and SELECTEDCONSTITUENT.ID not in(select ID from dbo.[' + @ATTRIBUTETABLENAME + '])';
end
else
begin
set @INSERTSQL = 'insert into dbo.[' + @ATTRIBUTETABLENAME + '] (ID, ' + @VALUECOLUMNNAME + ', ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + 'CURRENCYID, ';
set @INSERTSQL = @INSERTSQL + 'COMMENT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, STARTDATE, ENDDATE) select SELECTEDCONSTITUENT.ID, @VALUEPARAMETER, ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + '@CURRENCYIDPARAMETER, ';
set @INSERTSQL = @INSERTSQL + '@COMMENTPARAMETER, @CHANGEAGENTIDPARAMETER, @CHANGEAGENTIDPARAMETER, @CURRENTDATEPARAMETER, @CURRENTDATEPARAMETER, @STARTDATEPARAMETER, @ENDDATEPARAMETER from dbo.CONSTITUENT SELECTEDCONSTITUENT ';
/* We don't want to add a duplicate attribute value so we need to check if the value already exists before inserting the record. */
set @INSERTSQL = @INSERTSQL + ' where SELECTEDCONSTITUENT.ID not in(select ID from dbo.[' + @ATTRIBUTETABLENAME + '])';
end
/* If overwriting the exiting value we must only insert records that don't already exist in the attribute table */
if @OVERWRITEEXISTINGVALUE = 1 begin
set @UPDATESQL = 'update dbo.[' + @ATTRIBUTETABLENAME + '] set ' + @VALUECOLUMNNAME + ' = @VALUEPARAMETER, ';
if @DATATYPE = 3
set @UPDATESQL = @UPDATESQL + 'CURRENCYID = @CURRENCYIDPARAMETER, ';
set @UPDATESQL = @UPDATESQL + 'COMMENT = @COMMENTPARAMETER, CHANGEDBYID = @CHANGEAGENTIDPARAMETER, DATECHANGED = @CURRENTDATEPARAMETER, STARTDATE = @STARTDATEPARAMETER, ENDDATE = @ENDDATEPARAMETER from dbo.[' + @ATTRIBUTETABLENAME + '] SELECTEDCONSTITUENT ';
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0 begin
set @UPDATESQL = @UPDATESQL + @SECURITYCLAUSE;
end
end
end
else
begin
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @INSERTSQL = 'insert into dbo.[' + @ATTRIBUTETABLENAME + '] (ID, CONSTITUENTID, ' + @VALUECOLUMNNAME + ', ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + 'CURRENCYID, ';
set @INSERTSQL = @INSERTSQL + 'COMMENT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, STARTDATE, ENDDATE) select newid(), SELECTEDCONSTITUENT.ID, @VALUEPARAMETER, ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + '@CURRENCYIDPARAMETER, ';
set @INSERTSQL = @INSERTSQL + '@COMMENTPARAMETER, @CHANGEAGENTIDPARAMETER, @CHANGEAGENTIDPARAMETER, @CURRENTDATEPARAMETER, @CURRENTDATEPARAMETER, @STARTDATEPARAMETER, @ENDDATEPARAMETER from dbo.CONSTITUENT SELECTEDCONSTITUENT ' + @SECURITYCLAUSE;
end
else
begin
set @INSERTSQL = 'insert into dbo.[' + @ATTRIBUTETABLENAME + '] (ID, CONSTITUENTID, ' + @VALUECOLUMNNAME + ', ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + 'CURRENCYID, ';
set @INSERTSQL = @INSERTSQL + 'COMMENT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, STARTDATE, ENDDATE) select newid(), SELECTEDCONSTITUENT.ID, @VALUEPARAMETER, ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + '@CURRENCYIDPARAMETER, ';
set @INSERTSQL = @INSERTSQL + '@COMMENTPARAMETER, @CHANGEAGENTIDPARAMETER, @CHANGEAGENTIDPARAMETER, @CURRENTDATEPARAMETER, @CURRENTDATEPARAMETER, @STARTDATEPARAMETER, @ENDDATEPARAMETER from dbo.CONSTITUENT SELECTEDCONSTITUENT ';
end
/* If overwriting the existing value we must only insert records that don't already exist in the attribute table */
if @OVERWRITEEXISTINGVALUE = 1 begin
/* Don't insert new values for entries that will be overwritten */
set @INSERTSQL = @INSERTSQL + ' where SELECTEDCONSTITUENT.ID not in(select CONSTITUENTID from dbo.[' + @ATTRIBUTETABLENAME + '])';
set @UPDATESQL = 'update dbo.[' + @ATTRIBUTETABLENAME + '] set ' + @VALUECOLUMNNAME + ' = @VALUEPARAMETER, ';
if @DATATYPE = 3
set @UPDATESQL = @UPDATESQL + 'CURRENCYID = @CURRENCYIDPARAMETER, ';
set @UPDATESQL = @UPDATESQL + 'COMMENT = @COMMENTPARAMETER, CHANGEDBYID = @CHANGEAGENTIDPARAMETER, DATECHANGED = @CURRENTDATEPARAMETER, STARTDATE = @STARTDATEPARAMETER, ENDDATE = @ENDDATEPARAMETER from dbo.[' + @ATTRIBUTETABLENAME + '] SELECTEDCONSTITUENT ';
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0 begin
set @UPDATESQL = @UPDATESQL + @MULTIPLEATTRIBUTESECURITYCLAUSE
end
end
end
end
begin try
/* @VALUEPARAMETER definition must change based on attribute data type */
if @DATATYPE = 0
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER nvarchar(250)';
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + @SECURITYPARAMETERS;
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @STRINGVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @STRINGVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
else
begin
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @STRINGVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @STRINGVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
end
else if @DATATYPE = 1
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER int';
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + @SECURITYPARAMETERS;
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @NUMBERVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @NUMBERVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
else
begin
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @NUMBERVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @NUMBERVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
end
else if @DATATYPE = 2
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER datetime';
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + @SECURITYPARAMETERS;
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @DATEVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @DATEVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
else
begin
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @DATEVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @DATEVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
end
else if @DATATYPE = 3
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER money';
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + @SECURITYPARAMETERS;
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @CURRENCYIDPARAMETER = @CURRENCYID, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @MONEYVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @CURRENCYIDPARAMETER = @CURRENCYID, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @MONEYVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
else
begin
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @CURRENCYIDPARAMETER = @CURRENCYID, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @MONEYVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @CURRENCYIDPARAMETER = @CURRENCYID, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @MONEYVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
end
else if @DATATYPE = 4
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER bit';
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + @SECURITYPARAMETERS;
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @BOOLEANVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @BOOLEANVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
else
begin
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @BOOLEANVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @BOOLEANVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
end
else if @DATATYPE = 5
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER uniqueidentifier';
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + @SECURITYPARAMETERS;
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @CODETABLEVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @CODETABLEVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
else
begin
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @CODETABLEVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @CODETABLEVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
end
else if @DATATYPE = 6
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER uniqueidentifier';
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + @SECURITYPARAMETERS;
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @CONSTITUENTIDVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @CONSTITUENTIDVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
else
begin
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @CONSTITUENTIDVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @CONSTITUENTIDVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
end
else if @DATATYPE = 7
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER udt_fuzzydate';
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + @SECURITYPARAMETERS;
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @FUZZYDATEVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @FUZZYDATEVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
else
begin
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @FUZZYDATEVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @FUZZYDATEVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
end
else if @DATATYPE = 8
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER udt_hourminute';
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + @SECURITYPARAMETERS;
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @HOURMINUTEVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @HOURMINUTEVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
else
begin
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @HOURMINUTEVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @HOURMINUTEVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
end
else if @DATATYPE = 9
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER nvarchar(max)';
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + @SECURITYPARAMETERS;
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @MEMOVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @MEMOVALUE, @CURRENTAPPUSERIDPARAMETER = @CURRENTAPPUSERID, @BPIDPARAMETER = @BPID, @BYPASSSECURITYPARAMETER = @BYPASSSECURITY, @BYPASSSITESECURITYPARAMETER = @BYPASSSITESECURITY, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
else
begin
if @UPDATESQL <> '' begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @MEMOVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @VALUEPARAMETER = @MEMOVALUE, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE;
set @NUMBERADDED = @@ROWCOUNT;
end
end
if @SELECTION <> '' begin
set @SELECTIONTABLESQL = 'drop table ' + @SELECTION;
exec sp_executesql @SELECTIONTABLESQL
end
end try
begin catch
if @SELECTION <> '' begin
set @SELECTIONTABLESQL = 'drop table ' + @SELECTION;
exec sp_executesql @SELECTIONTABLESQL
end
exec dbo.USP_RAISE_ERROR;
return 1;
end catch