Dynamic SQL

  

Create Procedure [cdc].[columntracking]

/* ********************************************************************************************               

Name  : [cdc].[columntracking]             

Version : v0.1               

Date : 08-Jan-2014               

Author : Mohamed Faizal Enayathulla               

Purpose : collect the column information against the user table during DML operation               

How to use: Pass the first parameter as any usertable 

e.g. Exec [cdc].[columntracking] 'Test'        

******************************************************************************************** */                        

(  

@TableName VarChar(100)    

)  

AS begin  

  

 

Declare @SQL1 VarChar(1000)  

Declare @Join1 VarChar(1000)  

Declare @Join2 VarChar(1000)  

 

 

  

SELECT @SQL1='select [__$update_mask] ,  

        ( SELECT    CC.column_name +'',''  

          FROM      cdc.captured_columns CC  

                    INNER JOIN cdc.change_tables CT ON CC.[object_id] = CT.[object_id]  

          WHERE     capture_instance = ''dbo_'  

            

 select  @Join1='''  

          AND sys.fn_cdc_is_bit_set(CC.column_ordinal,  

                                              PD.__$update_mask) = 1  

        FOR  

          XML PATH('''')  

        ) AS changedcolumns  

      

FROM    cdc.dbo_'  

  

select  @Join2='_CT PD'  

  

  

  

select @SQL1=@SQL1+@TableName+@Join1+@TableName+@Join2  

  

Exec ( @SQL1)  

  

End

 

Create Procedure [cdc].[CDCAuditing]  

/* ********************************************************************************************             

Name  : cdc.CDCAuditing            

Version : v0.1             

Date : 08-Jan-2014             

Author : Mohamed Faizal Enayathulla             

Purpose : collect the logging records against the user table            

How to use: Pass the first parameter as any usertable , second and third should be time.

e.g. Exec cdc.CDCAuditing  'Test','2013-12-30','2014-1-8'       

******************************************************************************************** */                  

(      

@TableName nVarChar(100), 

@BeginDate DateTime, 

@EndDate DateTime         

)      

AS begin     

    

create table #columntracking    

(    

updatemask nvarchar(1000),    

columnchanged nvarchar(max)    

)    

--In order to get the column based on update_mask , the store procedure output

--loaded in to temp table     

Insert #columntracking    

Exec [cdc].[columntracking] @TableName    

    

    

    

Declare @SQL nVarChar(1000)      

Declare @Join nvarchar(1000)  

--Dynamic SQL is used below     

      

SELECT @SQL = 'SELECT distinct(TM.tran_begin_time ) ,     

      case [__$operation]      

      when 1 Then ''Delete''      

      when 2 Then ''Insert''      

      when 3 Then ''Before update''      

      when 4 Then ''After update''      

      End as [__$operation]      

      ,TM.tran_begin_time      

      ,TM.tran_end_time      

      ,M.[__$update_mask]    

      ,CT.columnchanged    

      ,M.*     

           

  FROM [cdc].[dbo_'      

      

select @join= '_CT] as M inner join cdc.lsn_time_mapping as TM     

on  TM.start_lsn=M.[__$start_lsn]    

Left outer join #columntracking as CT    

on CT.updatemask=M.[__$update_mask]  

WHERE  TM.tran_begin_time BETWEEN '''+convert(nvarchar(100), @BeginDate,109) + ''' 

and'''+ convert(nvarchar(100), @EndDate,109) + ''' and  

TM.tran_end_time BETWEEN '''+convert(nvarchar(100), @BeginDate,109) + ''' 

and'''+ convert(nvarchar(100), @EndDate,109) + ''' 

order by TM.tran_begin_time ,M.__$operation'      

    

SELECT @SQL = @SQL + @TableName +@join       

      

Exec sp_executesql  @SQL      

      

   

End