Dynamic Change Tracking Query error?

Anmol Ganju 1 Reputation point
2020-11-30T18:01:26.853+00:00

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?

Azure SQL Database
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2020-11-30T23:08:32.953+00:00

    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.

    0 comments
  2. Guoxiong 8,126 Reputation points
    2020-11-30T23:15:43.337+00:00

    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;
  3. MelissaMa-MSFT 24,176 Reputation points
    2020-12-01T01:51:52.993+00:00

    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

    0 comments