Handling Null in Dynamic SQL

Question

I am trying to capture the existing CDC value and disabling and enabling again for partition truncate. I could not figure out how to handle null values in dynamic SQL.

If I directly pass @role_name =NULL, & @filegroup_name = NULL. It is working fine.

-- Enable cdc for that table
 declare @cmd_cdc_enable nvarchar(max)
 SET @cmd_cdc_enable =N'
 exec sys.sp_cdc_enable_table 
 @source_schema = '''+@v_source_schema+''', 
 @source_name = '''+@v_source_table+''',
 @capture_instance = '''+@v_capture_instance +''',
 @supports_net_changes = 1,
 @role_name =NULL,
 @filegroup_name = NULL,
 @allow_partition_switch = 1

Sample Code:

GO
EXEC sys.sp_cdc_enable_db
GO
create schema [C1810429]
go
create table [C1810429].[STYTOTAL_RAW_NEW] (ID int identity primary key, name varchar(200))
-- Enable First time
go
EXEC sys.sp_cdc_enable_table 
 @source_schema = N'C1810429', 
 @source_name = N'STYTOTAL_RAW_NEW', 
 @capture_instance = 'C1810429_STYTOTAL_RAW_NEW',
 @supports_net_changes = 1 ,
 @role_name = NULL, 
 @filegroup_name = NULL, 
 @allow_partition_switch = 1
drop table if exists tbl_sp_cdc_help_change_data_capture
create table tbl_sp_cdc_help_change_data_capture (
source_schema sysname null, source_table sysname null, capture_instance sysname null, object_id int null, source_object_id int null, 
start_lsn binary(10) null, end_lsn binary(10) null, supports_net_changes bit null,has_drop_pending bit null, role_name sysname null, 
index_name sysname null, filegroup_name sysname null, create_date datetime null, index_column_list nvarchar(max) null, captured_column_list nvarchar(max) null
)
insert into tbl_sp_cdc_help_change_data_capture
Exec sys.sp_cdc_help_change_data_capture @source_schema='C1810429',@source_name='STYTOTAL_RAW_NEW'
-- select * from tbl_sp_cdc_help_change_data_capture
 declare @v_source_schema sysname
 declare @v_source_table sysname 
 declare @v_capture_instance sysname
 declare @v_supports_net_changes int
 declare @v_role_name sysname
 declare @v_filegroup_name sysname
 select @v_source_schema=source_schema,@v_source_table=source_table,@v_capture_instance=capture_instance,@v_supports_net_changes=supports_net_changes,
 @v_role_name=role_name,@v_filegroup_name=[filegroup_name]
 from tbl_sp_cdc_help_change_data_capture
 --select @v_source_schema,@v_source_table,@v_capture_instance,@v_supports_net_changes,@v_role_name,@v_filegroup_name
 /*
 -- disable cdc for that table
 declare @cmd_CDC_disable nvarchar(max)
 SET @cmd_CDC_disable =N'
 exec sys.sp_cdc_disable_table 
 @source_schema = '''+@v_source_schema+''', 
 @source_name = '''+@v_source_table+''',
 @capture_instance = '''+@v_capture_instance+'''
 '
 --print @cmd_CDC_disable
 EXEC sys.sp_executesql @cmd_CDC_disable
 select 'Disabled'
 */
 
 --select @v_source_schema,@v_source_table,@v_capture_instance,@v_supports_net_changes,@v_role_name,@v_filegroup_name
 -- Enable cdc for that table
 declare @cmd_cdc_enable nvarchar(max)
 SET @cmd_cdc_enable =N'
 exec sys.sp_cdc_enable_table 
 @source_schema = '''+@v_source_schema+''', 
 @source_name = '''+@v_source_table+''',
 @capture_instance = '''+@v_capture_instance +''',
 @supports_net_changes = 1,
 @role_name ='''+@v_role_name +''',
 @filegroup_name = '''+@v_filegroup_name +''',
 @allow_partition_switch = 1
 '
 print @cmd_cdc_enable
 EXEC sys.sp_executesql @cmd_cdc_enable
 --select 'Enabled'

• Charlieface: Why eaxctly are you using dynamic SQL here, can you not just execute the procedures directly?
• Saran: I could not, I need to get the old CDC configuration and disable, truncate then enable with old configuration
• Charlieface: So why can't you jus directly call exec sys.sp_cdc_enable_table @source_schema = @v_source_schema, @source_name = @v_source_table, @capture_instance = @v_capture_instance, @supports_net_changes = 1, @role_name = @v_role_name, @filegroup_name = @v_filegroup_name, @allow_partition_switch = 1 why does it need dynamic SQL, I don't understand?
• Saran: Hi @Charlieface, yes it is working fine. Even I do not know why I tried to do dynamic. Just to understand if you can when should I think of using dynamic SQL. if you can add some points that would help me to learn it.

Answer 1

Maybe I'm being dense, but why can't you just execute the procedures directly, without dynamic SQL?

You are also missing some other parameters if you want an identical setup from the original: @index_name and @captured_column_list

exec sys.sp_cdc_disable_table 
 @source_schema = @v_source_schema,
 @source_name = @v_source_table,
 @capture_instance = @v_capture_instance;
exec sys.sp_cdc_enable_table 
 @source_schema = @v_source_schema, 
 @source_name = @v_source_table,
 @capture_instance = @v_capture_instance,
 @supports_net_changes = 1,
 @role_name = @v_role_name,
 @index_name = @index_name,
 @captured_column_list = @captured_column_list,
 @filegroup_name = @v_filegroup_name,
 @allow_partition_switch = 1;

Answer 2

dynamic fix

This is how you'll want to rewrite the dynamic SQL bit. Notes to follow about why.

DECLARE
 @v_source_schema sysname = N'',
 @v_source_table sysname = N'',
 @v_capture_instance sysname = N'',
 @v_supports_net_changes bit = 'false',
 @v_role_name sysname = N'',
 @v_filegroup_name sysname = N'',
 @cmd_cdc_disable nvarchar(max) = N'
EXECUTE sys.sp_cdc_disable_table 
 @source_schema = @v_source_schema, 
 @source_name = @v_source_table,
 @capture_instance = @v_capture_instance;',
 @cmd_cdc_enable nvarchar(max) = N'
EXECUTE sys.sp_cdc_enable_table 
 @source_schema = @v_source_schema, 
 @source_name = @v_source_table,
 @capture_instance = @v_capture_instance,
 @supports_net_changes = 1,
 @role_name = @v_role_name,
 @filegroup_name = @v_filegroup_name,
 @allow_partition_switch = 1;',
 @cmd_cdc_disable_parameters nvarchar(max) = N'
 @v_source_schema sysname,
 @v_source_table sysname,
 @v_capture_instance sysname',
 @cmd_cdc_enable_parameters nvarchar(max) = N'
 @v_source_schema sysname,
 @v_source_table sysname,
 @v_capture_instance sysname,
 @v_supports_net_changes integer,
 @v_role_name sysname,
 @v_filegroup_name sysname';
SELECT 
 @v_source_schema = source_schema,
 @v_source_table = source_table,
 @v_capture_instance = capture_instance,
 @v_supports_net_changes = supports_net_changes,
 @v_role_name = role_name,
 @v_filegroup_name = [filegroup_name]
FROM tbl_sp_cdc_help_change_data_capture;
PRINT @cmd_cdc_disable;
EXECUTE sys.sp_executesql 
 @cmd_cdc_disable,
 @cmd_cdc_disable_parameters,
 @v_source_schema,
 @v_source_table,
 @v_capture_instance;
 
PRINT @cmd_cdc_enable;
EXEC sys.sp_executesql 
 @cmd_cdc_enable,
 @cmd_cdc_enable_parameters,
 @v_source_schema,
 @v_source_table,
 @v_capture_instance,
 @v_supports_net_changes,
 @v_role_name,
 @v_filegroup_name;

notes

As much as possible, you want to use parameterized dynamic SQL like this to avoid string truncation oddities, and protect yourself from SQL injection issues. Granted, you're working with well-controlled object names here where that's less of a concern, but you don't want to get into the habit of writing the bad kind of dynamic SQL.

If you're ever writing it elsewhere, and need to manage object names written into queries, you should always use QUOTENAME around each one individually to make each a a valid SQL Server delimited identifier.

• Saran: Thanks @Erik Reasonable Rates Darling. It is working fine and really helpful and I am testing and understanding your method of code.