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?
As far as this should be dynamic SQL (which I don't think it should), it should read:
Using parameterised statements is a lot easier.
It is, but look at what you do earlier in the loop.
If you are going to implement something in dynamic SQL, you should first write something which is perfectly static, so that you have that part correct, so that you know what you should generate.
This is one of the lessons about dynamic SQL, you will learn from my article, http://www.sommarskog.se/dynamic_sql.html.
HI @Sarvesh Pandey ,
Could you please provide any update about Erland's suggetion? Thanks.
Best regards
Melissa
Sign in to comment