Open topic with navigation

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>

Next: Configure and Run the SP Global Change