Dynamic Change Tracking Query error?

Question

We are trying to run the below query for Change Tracking:

DECLARE @CounterNumber INT = 1
WHILE @CounterNumber <= 3
BEGIN
Declare @DeezNutz varchar(255)
Set @DeezNutz = (Select Name from TableList where id = @CounterNumber)
SELECT CHANGE_TRACKING_CURRENT_VERSION() as CurrentChangeTrackingVersion
SELECT SYS_CHANGE_VERSION FROM [dbo].[table_store_ChangeTracking_version] WHERE TableName = @DeezNutz

DECLARE @sql NVARCHAR(MAX);  

 

DECLARE @currentVersion bigint    
Set @currentVersion = (SELECT TOP 1 SYS_CHANGE_VERSION  FROM table_store_ChangeTracking_version WHERE TableName=@name)  

 

DECLARE @newVersion bigint = CHANGE_TRACKING_CURRENT_VERSION()  

 

SET @sql = 'SELECT  
    Ct.sys_change_Operation,  
    CT.sys_change_version,  
    CT.id as ct_id,  
    @name.*      
    FROM @name  
    RIGHT OUTER JOIN CHANGETABLE(CHANGES dbo.@name, @currentVersion) CT ON @name.Id = CT.Id'  

 

SET @sql = REPLACE(@sql, '@name', quotename(@name))  
  
EXEC sp_executesql @sql  
set @CounterNumber = @CounterNumber + 1  

END;

But we are getting error stating:

Must declare the scalar variable "@currentVersion".

Can anyone help me understand what is wrong our here?

Answer 1

Didn't you ask a question about this the other day, and we told you that you should not use a dynamic query?

If you absolutely want to use dynamic SQL, you need to learn it first. There is more than one error in your code, and I'm short of time. On the other article, I have an article on my web site where you can learn to use dynamic SQL: http://www.sommarskog.se/dynamic_sql.html

Bear in mind that dynamic SQL is an advanced feature.

Answer 2

Try this:

DECLARE @CounterNumber INT = 1;
DECLARE @name varchar(255);
DECLARE @sql NVARCHAR(MAX);
DECLARE @currentVersion bigint;

WHILE @CounterNumber <= 3
BEGIN
    SELECT @name = [Name] FROM TableList WHERE id = @CounterNumber;
    --SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CurrentChangeTrackingVersion;
    --SELECT SYS_CHANGE_VERSION FROM [dbo].[table_store_ChangeTracking_version] WHERE TableName = @name;

    SET @currentVersion = (SELECT TOP 1 SYS_CHANGE_VERSION FROM table_store_ChangeTracking_version WHERE TableName = @name);
    --DECLARE @newVersion bigint = CHANGE_TRACKING_CURRENT_VERSION()

    SET @sql = 'SELECT
        Ct.sys_change_Operation,
        CT.sys_change_version,
        CT.id as ct_id,
        NM.*    
    FROM ' + QUOTENAME(@name) + ' NM
    RIGHT OUTER JOIN CHANGETABLE(CHANGES dbo.' + @name + ', ' + CAST(@currentVersion AS varchar(10)) + ') CT ON NM.Id = CT.Id';

    --SET @sql = REPLACE(@sql, '@name', quotename(@name))

    EXEC sp_executesql @sql;
    SET @CounterNumber = @CounterNumber + 1;
END;

Answer 3

Hi @Anmol Ganju ,

Thank you so much for posting here.

You could refer Guoxiong's query using QUOTENAME(variable) to avoid the error.

Besides, you could also refer below two forums and check whether any of them is helpful to you.

Using CHANGETABLE() on all tables
How to get a list of all changed tables from SQL Server Change Tracking

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table