Use change tracking in StoredProcedure only if is On for table in SQL Server

Question

I want to create an SP that will get data from ChangeTracking only if it is enabled for a given table.

In SP I have:

DECLARE @CHANGE_TRACKING_ENABLED BIT;
SELECT @CHANGE_TRACKING_ENABLED = is_track_columns_updated_on FROM sys.change_tracking_tables WHERE object_id = OBJECT_ID('MyTable')
if @CHANGE_TRACKING_ENABLED = 1
begin
    insert into @IDS select CT.[ID_MY_TABLE] FROM CHANGETABLE(CHANGES [MyTable], @last_change_version) as CT
end

But when I try to create procedure I have an error:

Change tracking is not enabled on table 'MyTable'.

What I'am doing wrong?

Answer 1

Because of the way the object binding works, the server is compiling the whole batch and failing before it even runs.

While you could use deferred object resolution for non-existent tables, this doesn't work if the table exists but change tracking is off.

You have two options:

• Use dynamic SQL. Less maintainable because refactoring can't see it, but you can keep it in one procedure.

DECLARE @CHANGE_TRACKING_ENABLED BIT;
SELECT @CHANGE_TRACKING_ENABLED = ct.is_track_columns_updated_on
FROM sys.change_tracking_tables ct
JOIN sys.tables t ON t.object_id = ct.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.name = N'MyTable'
  AND s.name = N'dbo';

IF @CHANGE_TRACKING_ENABLED = 1
BEGIN
    INSERT @IDS (ColumnsHere)
    EXEC sp_executesql N'
    SELECT CT.ID_MY_TABLE
    FROM CHANGETABLE(CHANGES [MyTable], @last_change_version) as CT;
';
END;

• Use another stored procedure. More maintainable, I would recommend this.

CREATE OR ALTER PROCEDURE dbo.GetChangeTracking_MyTable
  @last_change_version bigint
AS

SELECT CT.ID_MY_TABLE
FROM CHANGETABLE(CHANGES [MyTable], @last_change_version) as CT;

DECLARE @CHANGE_TRACKING_ENABLED BIT;
SELECT @CHANGE_TRACKING_ENABLED = ct.is_track_columns_updated_on
FROM sys.change_tracking_tables ct
JOIN sys.tables t ON t.object_id = ct.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.name = N'MyTable'
  AND s.name = N'dbo';

IF @CHANGE_TRACKING_ENABLED = 1
BEGIN
    INSERT @IDS (ColumnsHere)
    EXEC dbo.GetChangeTracking_MyTable @last_change_version;
END;