Hi ! It's been a long time since I last updated my blog. To continue with the Change Data Capture feature of SQL Server 2008, this is new post that helps you to know how you can write your own stored procedures / functions to get the required data in the fashion you want, using the CDC functions provided by Microsoft.

NOTE: If you haven't read the article "SQL Server 2008 - Change Data Capture - Part I" then I would recommend you to please visit the article first else you won't be able to connect well, what I am trying to express here.

The following function works in the similar fashion like cdc.fn_cdc_get_all_changes_InstanceHumanResourcesEmployee but this function will take the starting time and ending time i.e. the time range and the row filter option. The row filter option will either be 'all'  or 'all update old'.

cdc.fn_cdc_get_all_changes_InstanceHumanResourcesEmployee always matches the LSN number provided by you with the LSN present in the CDC table and if it is not found it will throw an error. The reason is it will check for the exact LSN Number so that duplicate records don't creep into the result set. The following function is more generic and will tell you about the changes that happened to a table within a time range. The logic is created in a fashion that if there are no records existing in the table matching the criteria specified by you then it will return an empty set.

This is just an example to demonstrate that how we could write our own SPs and Functions to get the data from the Change Tables.

/*

 

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

 

I hope people are enjoying to work with SQL Server 2008, just in case if you have missed these following links, please visit them today.

Keep writing to me & keep a watch on my blog. I will bring new articles, on new features of SQL Server 2008 very soon. Till then ... Bye !