USP_GLOBALCHANGE_DELETECONSTITUENTATTRIBUTE
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @IDSETREGISTERID | uniqueidentifier | IN | |
| @ATTRIBUTECATEGORYID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @ASOF | datetime | IN | |
| @NUMBERADDED | int | INOUT | |
| @NUMBEREDITED | int | INOUT | |
| @NUMBERDELETED | int | INOUT | |
| @CURRENTAPPUSERID | 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 | |
| @CURRENCYID | uniqueidentifier | IN | |
| @DELETETYPE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_GLOBALCHANGE_DELETECONSTITUENTATTRIBUTE
(
@IDSETREGISTERID uniqueidentifier = null,
@ATTRIBUTECATEGORYID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@ASOF as datetime = null,
@NUMBERADDED int = 0 output,
@NUMBEREDITED int = 0 output,
@NUMBERDELETED int = 0 output,
@CURRENTAPPUSERID uniqueidentifier = null,
@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,
@CURRENCYID uniqueidentifier = null,
@DELETETYPE tinyint = null
)
as
set nocount off;
declare @ATTRIBUTETABLENAME nvarchar(128)
declare @ONEPERRECORD bit
declare @DATATYPE int;
declare @VALUECOLUMNNAME nvarchar(128)
set @NUMBERADDED = 0;
set @NUMBEREDITED = 0;
set @NUMBERDELETED = 0;
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 @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 --JamesWill WI172781 2011-08-25 Be sure to select from CONSTITUENT first to make sure we get a null back for constituents without sites
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 --JamesWill WI172781 2011-08-25 Be sure to select from CONSTITUENT first to make sure we get a null back for constituents without sites
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
)
)
';
exec dbo.USP_SECURITY_APPUSER_BYPASSSECURITYFORBUSINESSPROCESS @CURRENTAPPUSERID, @BPID, @BYPASSSECURITY output, @BYPASSSITESECURITY output;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
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;
declare @SELECTIONTABLESQL nvarchar(100);
declare @SELECTION nvarchar(43);
set @SELECTION = '';
begin try
declare @CONTEXTCACHE varbinary(128);
declare @DELETESQL nvarchar(max);
if @IDSETREGISTERID is not null
begin
/* Create temp table to hold selection ID values */
declare @PARAMETERDEFINITION nvarchar(500);
set @PARAMETERDEFINITION = '@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 @PARAMETERDEFINITION = '@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, @PARAMETERDEFINITION, @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, @PARAMETERDEFINITION, @IDSETREGISTERIDPARAMETER = @IDSETREGISTERID;
end
if @ONEPERRECORD = 1
begin
set @DELETESQL = 'delete from dbo.[' + @ATTRIBUTETABLENAME + '] where ID in(select ID from ' + @SELECTION + ')';
end
else
begin
set @DELETESQL = 'delete from dbo.[' + @ATTRIBUTETABLENAME + '] where CONSTITUENTID in(select ID from ' + @SELECTION + ')';
end
-- append where clause when only certain values are to be deleted
if @DELETETYPE = 1
begin
if @DATATYPE = 0 and @STRINGVALUE <> ''
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + @STRINGVALUE + ''''
end
else if @DATATYPE = 1 and @NUMBERVALUE is not null
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ' + convert(nvarchar(max), @NUMBERVALUE)
end
else if @DATATYPE = 2 and @DATEVALUE is not null
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + convert(nvarchar(max), @DATEVALUE) + ''''
end
else if @DATATYPE = 3 and @MONEYVALUE is not null
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ' + convert(nvarchar(max), @MONEYVALUE)
end
else if @DATATYPE = 4 and @BOOLEANVALUE is not null
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ' + convert(nvarchar(max), case @BOOLEANVALUE when 'TRUE' then 1 else 0 end)
end
else if @DATATYPE = 5 and @CODETABLEVALUE is not null
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + convert(nvarchar(max), @CODETABLEVALUE) + ''''
end
else if @DATATYPE = 6 and @CONSTITUENTIDVALUE is not null
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + convert(nvarchar(max), @CONSTITUENTIDVALUE) + ''''
end
else if @DATATYPE = 7 and @FUZZYDATEVALUE <> '00000000'
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + convert(nvarchar(max), @FUZZYDATEVALUE) + ''''
end
else if @DATATYPE = 8 and @HOURMINUTEVALUE <> ''
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + convert(nvarchar(max), @HOURMINUTEVALUE) + ''''
end
else if @DATATYPE = 9 and @MEMOVALUE <> ''
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + @MEMOVALUE + ''''
end
end
/* Cache current context information@ */
set @CONTEXTCACHE = CONTEXT_INFO();
/* Set CONTEXT_INFO to @CHANGEAGENTID */
set CONTEXT_INFO @CHANGEAGENTID;
/* delete records */
exec sp_executesql @DELETESQL
set @NUMBERDELETED = @@ROWCOUNT;
/* Reset CONTEXT_INFO to previous value */
if not @contextCache is null begin
set CONTEXT_INFO @CONTEXTCACHE;
end
set @SELECTIONTABLESQL = 'drop table ' + @SELECTION;
exec sp_executesql @SELECTIONTABLESQL;
set @SELECTION = '';
end
else
begin
declare @DELETEPARAMETERDEFINITION nvarchar(500);
set @DELETEPARAMETERDEFINITION = '@CURRENTAPPUSERIDPARAMETER uniqueidentifier, @BPIDPARAMETER uniqueidentifier, @BYPASSSECURITYPARAMETER bit, @BYPASSSITESECURITYPARAMETER bit'
if @ONEPERRECORD = 1
-- delete all attribute records for which the user has rights
set @DELETESQL = 'delete from dbo.[' + @ATTRIBUTETABLENAME + '] where ID in
(
select SELECTEDCONSTITUENT.ID
from dbo.[' + @ATTRIBUTETABLENAME + '] SELECTEDCONSTITUENT ' + @SECURITYCLAUSE +
')';
else
-- delete all attribute records for which the user has rights
set @DELETESQL = 'delete from dbo.[' + @ATTRIBUTETABLENAME + '] where CONSTITUENTID in
(
select SELECTEDCONSTITUENT.CONSTITUENTID
from dbo.[' + @ATTRIBUTETABLENAME + '] SELECTEDCONSTITUENT ' + @MULTIPLEATTRIBUTESECURITYCLAUSE +
')';
-- append where clause when only certain values are to be deleted
if @DELETETYPE = 1
begin
if @DATATYPE = 0 and @STRINGVALUE <> ''
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + @STRINGVALUE + ''''
end
else if @DATATYPE = 1 and @NUMBERVALUE is not null
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ' + convert(nvarchar(max), @NUMBERVALUE)
end
else if @DATATYPE = 2 and @DATEVALUE is not null
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + convert(nvarchar(max), @DATEVALUE) + ''''
end
else if @DATATYPE = 3 and @MONEYVALUE is not null
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ' + convert(nvarchar(max), @MONEYVALUE)
end
else if @DATATYPE = 4 and @BOOLEANVALUE is not null
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ' + convert(nvarchar(max), case @BOOLEANVALUE when 'TRUE' then 1 else 0 end)
end
else if @DATATYPE = 5 and @CODETABLEVALUE is not null
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + convert(nvarchar(max), @CODETABLEVALUE) + ''''
end
else if @DATATYPE = 6 and @CONSTITUENTIDVALUE is not null
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + convert(nvarchar(max), @CONSTITUENTIDVALUE) + ''''
end
else if @DATATYPE = 7 and @FUZZYDATEVALUE <> '00000000'
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + convert(nvarchar(max), @FUZZYDATEVALUE) + ''''
end
else if @DATATYPE = 8 and @HOURMINUTEVALUE <> ''
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + convert(nvarchar(max), @HOURMINUTEVALUE) + ''''
end
else if @DATATYPE = 9 and @MEMOVALUE <> ''
begin
set @DELETESQL = @DELETESQL + ' and ' + @VALUECOLUMNNAME + ' = ''' + @MEMOVALUE + ''''
end
end
/* 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 begin
set CONTEXT_INFO @CONTEXTCACHE;
end
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