Code the SP Logic
Previous: Modify the SP Global Change Spec Header and Adding Parameters
Step 1 - Rename the stored procedure
Within the CREATE PROCEDURE statement, ensure the name of the stored procedure matches the value provided for the SPName attribute. Be sure to prefix the name of your database object with USR_. The named used in this stored procedure is USR_USP_GLOBALCHANGE_FOODBANK_DELETE_TRANSACTION_HISTORY.
Tip: For spec naming and coding standards, see Best Practices, 7 Commandments of Infinity Development and Commandment 5: Prefix Custom SQL Objects.
Step 2 - Replace the parameters to support the User Interface
Remove the @Field1 and @Field2 parameters and replace them with a parameter named @OLDERTHAN that matches the FieldID attribute value of OLDERTHAN used within our single FormField. When the global change instance is started, the system will pass the date value from the user interface to the stored procedure via the @OLDERTHAN parameter. The parameter value will be utilized within the WHERE clause of the processing logic.
Step 3 - Add the processing logic
Author a DELETE T-SQL statement that removes USR_FOODBANKTXHEADER records WHERE TXDATE < @OLDERTHAN. Any corresponding transaction detail rows within the USR_FOODBANKTXDETAIL table will be deleted as well via a cascade delete option established via the foreign key between the USR_FOODBANKTXHEADER and USR_FOODBANKTXDETAIL tables. See the blue highlights in the stored procedure below.
@NUMBERADDED, @NUMBEREDITED, and @NUMBERDELETED Parameters
Since our global change is deleting records we will need to report to the system the number of records deleted. If the logic was adding and/or updating records, we would need to report back these stats, as well. Note the use of the @NUMBERADDED, @NUMBEREDITED, and @NUMBERDELETED output parameters within the stored procedure. For more information, see the section "CreateProcedureSQL Element" in Exploring a GlobalChangeSpec. See the yellow highlights in the stored procedure below.
Tip: For details on how the number of rows added, edited, and deleted are stored by the system for each global change instance run, see the GlobalChangeRecordCount table within Global Change Tables.
@ASOF Parameter
When a global change instance is started, the system will pass the LASTRUNON value into the @ASOF parameter within a SP based global change. The software developer has the option to leverage the value of @ASOF within the logic of the global change definition to narrow the list of records processed. For example, the @ASOF value could be used within a SQL SELECT WHERE Clause to compare against a table's DATACHANGED column to see if any activity has taken place within the table since the last @ASOF date. You can reset the LASTRUNON date by selecting the instance in the Global Changes grid and clicking Reset last run on date. See the green highlights in the stored procedure below.
Tip: For more details, see the LastRunOn column in the GlobalChange table in Global Change Tables.
create procedure dbo.USR_USP_GLOBALCHANGE_FOODBANK_DELETE_TRANSACTION_HISTORY
(
@CHANGEAGENTID uniqueidentifier = null,
@ASOF as datetime = null,
@NUMBERADDED int = 0 output,
@NUMBEREDITED int = 0 output,
@NUMBERDELETED int = 0 output,
@OLDERTHAN date
)
as
set nocount off;
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate();
set @NUMBERADDED = 0;
set @NUMBEREDITED = 0;
set @NUMBERDELETED = 0;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
delete from USR_FOODBANKTXHEADER
where TXDATE < @OLDERTHAN set @NUMBERDELETED = @@ROWCOUNT
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
Step 4 - Review and load the completed GlobalChangeSpec
After you review the completed spec below, use LoadSpec to add the feature to your local instance of Blackbaud CRM.
<GlobalChangeSpec
xmlns="bb_appfx_globalchange"
xmlns:common="bb_appfx_commontypes"
ID="555e2416-3433-4467-8c51-c1fd54b98094"
Name="Food Bank Delete Transaction History Global Change (Custom)"
Description="Deletes old food bank transactions"
Author="Technical Training"
DisplayName="Delete food bank transaction history (Custom)"
GlobalChangeFolder="Food Bank"
SPName="USR_USP_GLOBALCHANGE_FOODBANK_DELETE_TRANSACTION_HISTORY"
>
<!-- describe the SP that performs the global change operation -->
<CreateProcedureSQL>
<![CDATA[
create procedure dbo.USR_USP_GLOBALCHANGE_FOODBANK_DELETE_TRANSACTION_HISTORY
(
@CHANGEAGENTID uniqueidentifier = null,
@ASOF as datetime = null,
@NUMBERADDED int = 0 output,
@NUMBEREDITED int = 0 output,
@NUMBERDELETED int = 0 output,
@OLDERTHAN date
)
as
set nocount off;
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate();
set @NUMBERADDED = 0;
set @NUMBEREDITED = 0;
set @NUMBERDELETED = 0;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
delete from USR_FOODBANKTXHEADER
where TXDATE < @OLDERTHAN
set @NUMBERDELETED = @@ROWCOUNT
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch]]>
</CreateProcedureSQL>
<ParametersFormMetaData>
<!-- describe fields on the parameter form, which correspond to parameters on the SP. Note that system parameters
like the context @CHANGEAGENTID, @ASOF, @NUMBERADDED, @NUMBEREDITED, and @NUMBERDELETED need not be listed. -->
<FormMetaData xmlns="bb_appfx_commontypes">
<FormFields>
<FormField FieldID="OLDERTHAN" Caption="Transactions older than" DataType="Date" />
</FormFields>
</FormMetaData>
</ParametersFormMetaData>
</GlobalChangeSpec>