我想创建一个SP，仅当为给定表启用了ChangeTracking时才从其获取数据.
在SP中，我拥有:
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
但是，当我try 创建过程时，我遇到一个错误:
Change tracking is not enabled on table 'MyTable'.
我做错了什么？
由于对象绑定的工作方式，服务器正在编译整个批处理，甚至在它运行之前就失败了.
虽然您可以对不存在的表使用延迟对象解析，但如果表存在但更改跟踪关闭，则这不起作用.
您有两个 Select :
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;
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;