I wanted to use DB Name variable while executing the Stored Procedure sys.sp_cdc_enable_table for 100 Tables

Question

Below is the small part of the logic of an procedure, I'm trying to run sp_cdc_enable_table SP for 100 Tables on the provided Database as variable.

I wanted to pass database name as variable and directly execute the sp_cdc_enable_table. If I do it without giving the database name it tries to enable CDC on MASTER Database instead of variable passed database.

CREATE PROCEDURE [dbo].[my_SP]  
(  
    @V1 NVARCHAR(100),
    @V2 NVARCHAR(100),
    @DBName NVARCHAR(500)
)
BEGIN

    exec @DBName+.[sys].[sp_cdc_enable_table] @source_schema =  'dbo',  @source_name =  'Sample', @role_name = NULL, @supports_net_changes =  0, @captured_column_list =  '[Code1], [Code2]';

    -- But giving Syntax Error OR

    exec 'ABC_'+@V1+'_'+@V2+'_XYZ'.sys.sp_cdc_enable_table @source_schema =  'dbo',  @source_name =  'Category', @role_name = NULL, @supports_net_changes =  0, @captured_column_list =  '[Category1], [Category2]';

    -- But giving Syntax Error
END

Expectation

--EXEC my_SP @V1= 'TEST', @V2 = 'TEST11',  @DBName= 'ABC_TEST_TEST11_XYZ'

I can't use sp_executesql because I need to run sp_cdc_enable_table SP for 100 Tables with lot many of columns. In that case @Query can't hold all Script. I want it to execute directly like above.

Answer 1

Trying it with DynamicSQL could solve it ? You can use parameters V1 and V2 by replacing your parameters in the sample code below

DECLARE @SQL VARCHAR(8000), @dbName as varchar(50)
SET @dbName = 'db'

SET @SQL = 'exec ' + @DBName + '.[sys].[sp_cdc_enable_table] @source_schema =  ''dbo'',  @source_name =  ''Sample'', @role_name = NULL, @supports_net_changes =  0, @captured_column_list =  ''[Code1], [Code2]'''
PRINT @SQL
EXEC @SQL