USP_GLOBALCHANGE_ADDORCHANGEEVENTATTRIBUTE
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SELECTEDEVENTS | xml | IN | |
| @ATTRIBUTECATEGORYID | uniqueidentifier | IN | |
| @STRINGVALUE | nvarchar(250) | IN | |
| @NUMBERVALUE | int | IN | |
| @MONEYVALUE | money | IN | |
| @CURRENCYID | uniqueidentifier | 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 | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @RULESFOREXISTINGVALUES | tinyint | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @ASOF | datetime | IN | |
| @NUMBERADDED | int | INOUT | |
| @NUMBEREDITED | int | INOUT | |
| @NUMBERDELETED | int | INOUT | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_GLOBALCHANGE_ADDORCHANGEEVENTATTRIBUTE
(
@SELECTEDEVENTS xml = null,
@ATTRIBUTECATEGORYID uniqueidentifier,
@STRINGVALUE nvarchar(250) = null,
@NUMBERVALUE int = null,
@MONEYVALUE money = null,
@CURRENCYID uniqueidentifier = 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,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@RULESFOREXISTINGVALUES tinyint = 0,
@CHANGEAGENTID uniqueidentifier = null,
@ASOF as datetime = null,
@NUMBERADDED int = 0 output,
@NUMBEREDITED int = 0 output,
@NUMBERDELETED int = 0 output,
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount off;
/*
@RULESFOREXISTINGVALUES
0: Do not update or add attribute
1: Update existing attribute value
2: Add new attribute
*/
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 @CURRENTDATE = getdate();
set @NUMBERADDED = 0;
set @NUMBEREDITED = 0;
set @NUMBERDELETED = 0;
if @CHANGEAGENTID is null begin
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
end
--Find the table name and data type for the attribute category
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;
/* Definitions for common parameters */
set @PARAMETERDEFINITION = '@COMMENTPARAMETER nvarchar(255), @STARTDATEPARAMETER datetime, @ENDDATEPARAMETER datetime, @CHANGEAGENTIDPARAMETER uniqueidentifier, @CURRENTDATEPARAMETER datetime, @SELECTEDEVENTSPARAMETER xml, @CURRENTAPPUSERIDPARAMETER uniqueidentifier';
if @DATATYPE = 3
set @PARAMETERDEFINITION = '@CURRENCYIDPARAMETER uniqueidentifier, ' + @PARAMETERDEFINITION;
/* SQLs for specific events */
if @ONEPERRECORD = 1
begin
set @INSERTSQL = 'insert into dbo.[' + @ATTRIBUTETABLENAME + '] (ID, ' + @VALUECOLUMNNAME + ', ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + 'CURRENCYID, ';
set @INSERTSQL = @INSERTSQL + 'COMMENT, STARTDATE, ENDDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) select EVENTCOLLECTION.EVENTID, @VALUEPARAMETER, ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + '@CURRENCYIDPARAMETER, ';
set @INSERTSQL = @INSERTSQL + '@COMMENTPARAMETER, @STARTDATEPARAMETER, @ENDDATEPARAMETER, @CHANGEAGENTIDPARAMETER, @CHANGEAGENTIDPARAMETER, @CURRENTDATEPARAMETER, @CURRENTDATEPARAMETER from dbo.UFN_GLOBALCHANGE_GETEVENTLIST_FROMITEMLISTXML(@SELECTEDEVENTSPARAMETER) as EVENTCOLLECTION';
/* filter out results with site security check */
set @INSERTSQL = @INSERTSQL + ' WHERE exists( select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(EVENTCOLLECTION.EVENTID) EVENTSITE where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERIDPARAMETER) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERIDPARAMETER,''800093A6-B727-490B-8CC4-C0C0CF2148F0'',20) where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null))))'
/* 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 EVENTCOLLECTION.EVENTID 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 @RULESFOREXISTINGVALUES = 1
begin
set @UPDATESQL = 'update dbo.[' + @ATTRIBUTETABLENAME + '] set ' + @VALUECOLUMNNAME + ' = @VALUEPARAMETER, ';
if @DATATYPE = 3
set @UPDATESQL = @UPDATESQL + 'CURRENCYID = @CURRENCYIDPARAMETER, ';
set @UPDATESQL = @UPDATESQL + 'COMMENT = @COMMENTPARAMETER, STARTDATE = @STARTDATEPARAMETER, ENDDATE = @ENDDATEPARAMETER, CHANGEDBYID = @CHANGEAGENTIDPARAMETER, DATECHANGED = @CURRENTDATEPARAMETER where [' + @ATTRIBUTETABLENAME + '].ID in';
/* get the selected events from the XML document and filter out those events that the given user does not have access to (based on feature/site security) */
set @UPDATESQL = @UPDATESQL + ' (select EVENTCOLLECTION.EVENTID from dbo.UFN_GLOBALCHANGE_GETEVENTLIST_FROMITEMLISTXML(@SELECTEDEVENTSPARAMETER) as EVENTCOLLECTION WHERE exists( select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(EVENTCOLLECTION.EVENTID) EVENTSITE where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERIDPARAMETER) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERIDPARAMETER,''800093A6-B727-490B-8CC4-C0C0CF2148F0'',20) where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)))))';
end
end
/* Multi-record value type */
else
begin
set @INSERTSQL = 'insert into dbo.[' + @ATTRIBUTETABLENAME + '] (ID, EVENTID, ' + @VALUECOLUMNNAME + ', ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + 'CURRENCYID, ';
set @INSERTSQL = @INSERTSQL + 'COMMENT, STARTDATE, ENDDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) select newid(), EVENTCOLLECTION.EVENTID, @VALUEPARAMETER, ';
if @DATATYPE = 3
set @INSERTSQL = @INSERTSQL + '@CURRENCYIDPARAMETER, ';
set @INSERTSQL = @INSERTSQL + '@COMMENTPARAMETER, @STARTDATEPARAMETER, @ENDDATEPARAMETER, @CHANGEAGENTIDPARAMETER, @CHANGEAGENTIDPARAMETER, @CURRENTDATEPARAMETER, @CURRENTDATEPARAMETER from dbo.UFN_GLOBALCHANGE_GETEVENTLIST_FROMITEMLISTXML(@SELECTEDEVENTSPARAMETER) as EVENTCOLLECTION';
/* filter out results with site security check */
set @INSERTSQL = @INSERTSQL + ' WHERE exists( select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(EVENTCOLLECTION.EVENTID) EVENTSITE where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERIDPARAMETER) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERIDPARAMETER,''800093A6-B727-490B-8CC4-C0C0CF2148F0'',20) where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null))))'
/* For rules #0 and #1, we don't want to add a duplicate attribute value so we need to check if the value already exists before inserting the record. */
if @RULESFOREXISTINGVALUES <> 2
set @INSERTSQL = @INSERTSQL + ' and EVENTCOLLECTION.EVENTID not in(select EVENTID from dbo.[' + @ATTRIBUTETABLENAME + '])'
/* If overwriting the exiting value we must only insert records that don't already exist in the attribute table */
if @RULESFOREXISTINGVALUES = 1
begin
set @UPDATESQL = 'update dbo.[' + @ATTRIBUTETABLENAME + '] set ' + @VALUECOLUMNNAME + ' = @VALUEPARAMETER, ';
if @DATATYPE = 3
set @UPDATESQL = @UPDATESQL + 'CURRENCYID = @CURRENCYIDPARAMETER, ';
set @UPDATESQL = @UPDATESQL + 'COMMENT = @COMMENTPARAMETER, STARTDATE = @STARTDATEPARAMETER, ENDDATE = @ENDDATEPARAMETER, CHANGEDBYID = @CHANGEAGENTIDPARAMETER, DATECHANGED = @CURRENTDATEPARAMETER where [' + @ATTRIBUTETABLENAME + '].EVENTID in';
/* get the selected events from the XML document and filter out those events that the given user does not have access to (based on feature/site security) */
set @UPDATESQL = @UPDATESQL + ' (select EVENTCOLLECTION.EVENTID from dbo.UFN_GLOBALCHANGE_GETEVENTLIST_FROMITEMLISTXML(@SELECTEDEVENTSPARAMETER) as EVENTCOLLECTION WHERE exists( select 1 from dbo.UFN_SITEID_MAPFROM_EVENTID(EVENTCOLLECTION.EVENTID) EVENTSITE where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERIDPARAMETER) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERIDPARAMETER,''800093A6-B727-490B-8CC4-C0C0CF2148F0'',20) where SITEID=[EVENTSITE].[SITEID] or (SITEID is null and [EVENTSITE].[SITEID] is null)))))';
end
end
/* Build the parameter definitions based on value type and execute SQLs by passing in different value variable */
begin try
if @DATATYPE = 0 --Text
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER nvarchar(250)';
if @UPDATESQL <> ''
begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @STRINGVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @STRINGVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBERADDED = @@ROWCOUNT;
end
else if @DATATYPE = 1 --Number
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER int';
if @UPDATESQL <> ''
begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @NUMBERVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @NUMBERVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBERADDED = @@ROWCOUNT;
end
else if @DATATYPE = 2 --Date
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER datetime';
if @UPDATESQL <> ''
begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @DATEVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @DATEVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBERADDED = @@ROWCOUNT;
end
else if @DATATYPE = 3 --Currency
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER money';
if @UPDATESQL <> ''
begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @MONEYVALUE, @CURRENCYIDPARAMETER = @CURRENCYID, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @MONEYVALUE, @CURRENCYIDPARAMETER = @CURRENCYID, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBERADDED = @@ROWCOUNT;
end
else if @DATATYPE = 4 --Boolean
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER bit';
if @UPDATESQL <> ''
begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @BOOLEANVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @BOOLEANVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBERADDED = @@ROWCOUNT;
end
else if @DATATYPE = 5 --CodeTable
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER uniqueidentifier';
if @UPDATESQL <> ''
begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @CODETABLEVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @CODETABLEVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBERADDED = @@ROWCOUNT;
end
else if @DATATYPE = 6 --ConstituentRecord
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER uniqueidentifier';
if @UPDATESQL <> ''
begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @CONSTITUENTIDVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @CONSTITUENTIDVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBERADDED = @@ROWCOUNT;
end
else if @DATATYPE = 7 --FuzzyDate
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER udt_fuzzydate';
if @UPDATESQL <> ''
begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @FUZZYDATEVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @FUZZYDATEVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBERADDED = @@ROWCOUNT;
end
else if @DATATYPE = 8 --Time
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER udt_hourminute';
if @UPDATESQL <> ''
begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @HOURMINUTEVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @HOURMINUTEVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBERADDED = @@ROWCOUNT;
end
else if @DATATYPE = 9 --Memo
begin
set @PARAMETERDEFINITION = @PARAMETERDEFINITION + ', @VALUEPARAMETER nvarchar(max)';
if @UPDATESQL <> ''
begin
exec sp_executesql @UPDATESQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @MEMOVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBEREDITED = @@ROWCOUNT;
end
exec sp_executesql @INSERTSQL, @PARAMETERDEFINITION, @COMMENTPARAMETER = @COMMENT, @STARTDATEPARAMETER = @STARTDATE, @ENDDATEPARAMETER = @ENDDATE, @CHANGEAGENTIDPARAMETER = @CHANGEAGENTID, @CURRENTDATEPARAMETER = @CURRENTDATE, @SELECTEDEVENTSPARAMETER=@SELECTEDEVENTS, @VALUEPARAMETER = @MEMOVALUE, @CURRENTAPPUSERIDPARAMETER=@CURRENTAPPUSERID;
set @NUMBERADDED = @@ROWCOUNT;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch