通过单个存储过程从多个表中更改数据捕获？

发布于 2024-10-22 05:33:11 字数 915 浏览 1 评论 0原文

我需要检索多个表的更改数据捕获行，并且（根据公司 IT 政策）要求我通过存储过程访问数据库。我宁愿创建一个以表名作为参数的单个存储过程，而不是为我正在监视的每个表创建一个存储过程。我困惑的地方是 CDC 为每个受监视的表定义了一个单独的表值函数名称，并且我不确定如何最好地概括它。

是否可以修改以下示例代码，以便调用 cdc.fn_cdc_get_net_changes_dbo_ + @Table 而不是 cdc.fn_cdc_get_net_changes_dbo_TABLE ？

我应该使用另一种方法吗？

create proc [dbo].GetChangesForTable
 @Table varchar(50),
 @BeginTime datetime,
 @EndTime datetime
as
begin

    DECLARE @begin_lsn binary(10), @end_lsn binary(10); 
    DECLARE @func nvarchar(128)

    if @EndTime is null select @EndTime=GETDATE()

    SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @BeginTime); 
    SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @EndTime); 

     -- HOW TO GET THE CORRECT FUNCTION CALLED HERE?        
    SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_TABLE(@begin_lsn, @end_lsn, 'all')

end
GO

原文

I need to retrieve change-data-capture rows for several tables, and I'm required (by company IT policy) to access the database via stored procedures. I would rather create a single stored procedure with the table name as a parameter, rather than one stored procedure for each table I'm monitoring. Where I get hung up is that CDC defines a separate table-valued function name for each table monitored, and I'm not sure how best to generalize around that.

Is it possible to modify the following example code so that it invokes cdc.fn_cdc_get_net_changes_dbo_ + @Table instead of cdc.fn_cdc_get_net_changes_dbo_TABLE ?

Is there another approach I should use?

create proc [dbo].GetChangesForTable
 @Table varchar(50),
 @BeginTime datetime,
 @EndTime datetime
as
begin

    DECLARE @begin_lsn binary(10), @end_lsn binary(10); 
    DECLARE @func nvarchar(128)

    if @EndTime is null select @EndTime=GETDATE()

    SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @BeginTime); 
    SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @EndTime); 

     -- HOW TO GET THE CORRECT FUNCTION CALLED HERE?        
    SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_TABLE(@begin_lsn, @end_lsn, 'all')

end
GO

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寂寞花火° 2024-10-29 05:33:11

我认为使用 sp_executesql 是可能的：

DECLARE @sql nvarchar(4000)
    SET @sql = N'SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_'+cast(@TABLE as varchar)+'(@begin_lsn, @end_lsn, 'all')'


EXEC sp_executesql @sql, N'@Table varchar(50), @BeginTime datetime, @EndTime datetime',@Table,@BeginTime,@EndTime

I think that's possible with sp_executesql like that :

DECLARE @sql nvarchar(4000)
    SET @sql = N'SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_'+cast(@TABLE as varchar)+'(@begin_lsn, @end_lsn, 'all')'


EXEC sp_executesql @sql, N'@Table varchar(50), @BeginTime datetime, @EndTime datetime',@Table,@BeginTime,@EndTime

回复收藏 0 原文

~没有更多了~