Sample: Stored procedure user exit

create procedure 
PROD.AUDIT_STPROC 
( @result INT OUTPUT, 
@returnMsg VARCHAR(255) 
OUTPUT, @s$entry INT, 
@s$srcSysId VARCHAR, 
@s$srcTabId VARCHAR, 
@s$tgtTabId VARCHAR, 
@j$ENTT CHAR(2) @a$IDNO 
INT, @a$PRICE DECIMAL(10,2), 
@a$DESC VARCHAR, 
@a$LONGDESC VARCHAR, 
@a$TRANSDATE DATETIME, 
@d$IDNO INT, @d$PRICE 
DECIMAL(10,2), @d$DESC 
VARCHAR, @d$LONGDESC 
VARCHAR, @d$TRANSDATE 
DATETIME )

The parameters you declare and want to pass to your stored procedure must be valid data types.

The following parameters have been declared in this stored procedure:

@s$entry

Retrieves the entry point at which the stored procedure was called. In this example, CDC Replication calls the user exit at every entry point.

@s$srcSysId

Retrieves the location of source data.

@s$srcTabId

Retrieves the name of the source table.

@s$tgtTabId

Retrieves the name of the target table.

@j$ENTT

Retrieves the journal code that indicates the type of operation on the source table.

@a$

Retrieves the after image of the IDNO, PRICE, DESC, LONGDESC, and TRANSDATE source columns.

@d$

Retrieves the transformed data of the IDNO, PRICE, DESC, LONGDESC, and TRANSDATA target columns.

AS
declare @ENTRYPOINT VARCHAR(50);
BEGIN

select @ENTRYPOINT = case @s$entry
WHEN 3 THEN 'User Exit program 
called Before Insert'
WHEN 4 THEN 'User Exit program 
called After Insert'
WHEN 5 THEN 'User Exit program 
called Before Update'
WHEN 6 THEN 'User Exit program 
called After Update'

END

This stored procedure user exit can be invoked from these entry points.

insert into PROD.AUDIT_TABLE1 
values (
@s$entry, 
@s$srcSysId, @s$srcTabId, 
@s$tgtTabId,
@j$ENTT, @a$IDNO, 
@a$PRICE, @a$DESC, 
@a$LONGDESC,
@a$TRANSDATE, 
@d$IDNO, @d$PRICE, 
@d$DESC,
@d$LONGDESC, 
@d$TRANSDATE,
@ENTRYPOINT)

This stored procedure user exit will INSERT these values into PROD.AUDIT_TABLE1.

set @result = 0
set @returnMsg = 'OK'

END

This stored procedure user exit is successful and returns a '0' value.