CDC changes column-by-column instead of row-by-row, can you improve it???

,

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

Rate

Share

Share

Rate