I’m writing a table value function that returns a cdc recordset that, instead of returning changed data row-by-row, I need to output them column-by-column.
Say for example I have a table called auth.Account with cdc enabled on fields Email and Password. e.g.
EXEC sys.sp_cdc_enable_table @source_schema = N’auth’,
@source_name = N’Account’, @captured_column_list = N’
Email,
Password
@supports_net_changes = 1;
Right, now cdc is enabled, i need my recordset to look like this:-
ID, CDC_OPERATION, LSN, CurrentEmail, PreviousEmail, CurrentPassword, PreviousPassword
This is my solution so far, yep I know its a bit messy and could be improved, any ideas on how I could improve this? Thanks AJF
— =========================================
— Author: Andrew Fenna
— Create date: 2013-08-15
— Description: Get Changed Auth Details TVF
— =========================================
ALTER FUNCTION [auth].[ChangedAuthDetails] ( @FromLsn BINARY(10) )
RETURNS @User TABLE
(
UserID INT ,
CDC_OPERATION CHAR(1) ,
LSN BINARY(10) ,
CurrentEmail NVARCHAR(255) ,
PreviousEmail NVARCHAR(255) ,
CurrentPassword NVARCHAR(32) ,
PreviousPassword NVARCHAR(32)
)
AS
BEGIN
DECLARE @ToLsn BINARY(10)
IF ( @FromLsn IS NULL )
SELECT @FromLsn = sys.fn_cdc_get_min_lsn(N’auth_account’)
ELSE
SELECT @FromLsn = sys.fn_cdc_increment_lsn(@FromLsn)
SELECT @ToLsn = sys.fn_cdc_get_max_lsn()
IF ( @FromLsn = sys.fn_cdc_increment_lsn(@ToLsn) )
RETURN
— Query for change data
INSERT INTO @User
SELECT a.UserID ,
‘U’ AS [__$operation] ,
a.[__$start_lsn] ,
A.CurrentEmail ,
a.PreviousEmail ,
b.CurrentPassword ,
b.PreviousPassword
FROM ( SELECT Email.[__$start_lsn] ,
Email.ID AS [UserID] ,
Email.[3] AS [PreviousEmail] ,
Email.[4] AS [CurrentEmail]
FROM ( SELECT [__$start_lsn] ,
ID ,
Email ,
[__$operation]
FROM cdc.fn_cdc_get_all_changes_auth_account(@FromLsn,
@ToLsn, ‘all update old’)
WHERE [__$operation] IN ( 3, 4 )
) P PIVOT
( MAX(Email) FOR [__$operation] IN ( [3], [4] ) ) AS Email
) A
CROSS APPLY ( SELECT [password].[__$start_lsn] ,
[Password].[3] AS [PreviousPassword] ,
[Password].[4] AS [CurrentPassword]
FROM ( SELECT [__$start_lsn] ,
ID ,
[Password] ,
[__$operation]
FROM cdc.fn_cdc_get_all_changes_auth_account(@FromLsn,
@ToLsn, ‘all update old’)
WHERE [__$operation] IN ( 3, 4 )
) P PIVOT
( MAX([Password]) FOR [__$operation] IN ( [3], [4] ) ) AS [Password]
) B
WHERE A.__$start_lsn = B.__$start_lsn
UNION
SELECT [ID] AS [UserID] ,
CASE __$operation
WHEN 1 THEN ‘D’
WHEN 2 THEN ‘I’
ELSE NULL
END AS CDC_OPERATION,
[__$start_lsn] ,
CASE [__$operation]
WHEN 1 THEN NULL
WHEN 2 THEN Email
END AS [CurrentEmail] ,
CASE [__$operation]
WHEN 1 THEN Email
WHEN 2 THEN NULL
END AS [PreviousEmail] ,
CASE [__$operation]
WHEN 1 THEN NULL
WHEN 2 THEN [Password]
END AS [CurrentPassword] ,
CASE [__$operation]
WHEN 1 THEN [Password]
WHEN 2 THEN NULL
END AS [PreviousPassword]
FROM cdc.fn_cdc_get_all_changes_auth_account(@FromLsn,
@ToLsn, ‘all update old’)
WHERE [__$operation] < 3
RETURN
END
