| /* This function will return all the changes happened to the table within a specific time period. It will accept any valid date and time range and accepts row filters -> all -> all update old */ create function [cdc].[fn_cdc_get_changes_within_time_period] ( @from_time datetime2(7), @to_time datetime2(7), @row_filter_option nvarchar(30) ) RETURNS @return_table TABLE ( [__$start_lsn] [binary](10) NOT NULL, [__$seqval] [binary](10) NOT NULL, [__$operation] [int] NOT NULL, [__$update_mask] [varbinary](128) NULL, [EmployeeID] [int] NULL, [NationalIDNumber] [nvarchar](15) NULL, [ContactID] [int] NULL, [LoginID] [nvarchar](256) NULL, [ManagerID] [int] NULL, [Title] [nvarchar](50) NULL, [BirthDate] [datetime] NULL, [MaritalStatus] [nchar](1) NULL, [Gender] [nchar](1) NULL, [HireDate] [datetime] NULL, [SalariedFlag] [bit] NULL, [VacationHours] [smallint] NULL, [SickLeaveHours] [smallint] NULL, [CurrentFlag] [bit] NULL, [rowguid] [uniqueidentifier] NULL, [ModifiedDate] [datetime] NULL ) AS BEGIN DECLARE @from_lsn binary(10), @to_lsn binary(10); SELECT @from_lsn = MIN([__$start_lsn]) FROM cdc.InstanceHumanResourcesEmployee_CT WHERE sys.fn_cdc_map_lsn_to_time ([__$start_lsn]) >= @from_time AND sys.fn_cdc_map_lsn_to_time ([__$start_lsn]) <= @to_time ; SELECT @to_lsn = MAX([__$start_lsn]) FROM cdc.InstanceHumanResourcesEmployee_CT WHERE sys.fn_cdc_map_lsn_to_time ([__$start_lsn]) <= @to_time AND sys.fn_cdc_map_lsn_to_time ([__$start_lsn]) >= @from_time ; IF ISNULL(@from_lsn,0) <> 0 AND ISNULL(@to_lsn,0) <> 0 AND @from_time <= @to_time BEGIN INSERT INTO @return_table select NULL as __$start_lsn, NULL as __$seqval, NULL as __$operation, NULL as __$update_mask, NULL as [EmployeeID], NULL as [NationalIDNumber], NULL as [ContactID], NULL as [LoginID], NULL as [ManagerID], NULL as [Title], NULL as [BirthDate], NULL as [MaritalStatus], NULL as [Gender], NULL as [HireDate], NULL as [SalariedFlag], NULL as [VacationHours], NULL as [SickLeaveHours], NULL as [CurrentFlag], NULL as [rowguid], NULL as [ModifiedDate] where ( [sys].[fn_cdc_check_parameters] ( N'InstanceHumanResourcesEmployee', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 0) = 0)
union all select t.__$start_lsn as __$start_lsn, t.__$seqval as __$seqval, t.__$operation as __$operation, t.__$update_mask as __$update_mask, t.[EmployeeID], t.[NationalIDNumber], t.[ContactID], t.[LoginID], t.[ManagerID], t.[Title], t.[BirthDate], t.[MaritalStatus], t.[Gender], t.[HireDate], t.[SalariedFlag], t.[VacationHours], t.[SickLeaveHours], t.[CurrentFlag], t.[rowguid], t.[ModifiedDate] from [cdc].[InstanceHumanResourcesEmployee_CT] t where (lower(rtrim(ltrim(@row_filter_option))) = 'all') and ( [sys].[fn_cdc_check_parameters]( N'InstanceHumanResourcesEmployee', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 0) = 1) and (t.__$operation = 1 or t.__$operation = 2 or t.__$operation = 4) and (t.__$start_lsn <= @to_lsn) and (t.__$start_lsn >= @from_lsn) union all select t.__$start_lsn as __$start_lsn, t.__$seqval as __$seqval, t.__$operation as __$operation, t.__$update_mask as __$update_mask, t.[EmployeeID], t.[NationalIDNumber], t.[ContactID], t.[LoginID], t.[ManagerID], t.[Title], t.[BirthDate], t.[MaritalStatus], t.[Gender], t.[HireDate], t.[SalariedFlag], t.[VacationHours], t.[SickLeaveHours], t.[CurrentFlag], t.[rowguid], t.[ModifiedDate] from [cdc].[InstanceHumanResourcesEmployee_CT] t where (lower(rtrim(ltrim(@row_filter_option))) = 'all update old') and ( [sys].[fn_cdc_check_parameters]( N'InstanceHumanResourcesEmployee', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 0) = 1) and (t.__$operation = 1 or t.__$operation = 2 or t.__$operation = 4 or t.__$operation = 3 ) and (t.__$start_lsn <= @to_lsn) and (t.__$start_lsn >= @from_lsn) END RETURN END GO
| 