This might sound silly. However I am trying to trying to take the last modified date value from the comparison of both tables, however I get the "must declare the scalar variable @pa' error. I have lot more columns and that is the reason i am using cursor to check
declare @pa varchar(50)
declare audit_cur cursor for
select distinct audit_field
from #Iam
where concat(',',@af,',') like concat('%,',audit_field,',%')
OPEN audit_cur
FETCH NEXT FROM audit_cur into @pa
declare @cmd varchar(1000)
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd=concat('SELECT a.A_n,i.audit_field,a.',@pa,' field_value,i.field_after
FROM #Iam_audit a
JOIN (Select a.A_n, a.field_after,audit_field
from #iam a(nolock)
inner join (Select a_n, max(Modified_Date) as maxdate
from #iam a2(nolock)
where a2.Audit_field=@pa
group by a_n
) as aa on aa.a_n = a.a_n and aa.maxdate=a.modified_Date
where a.Audit_Field=@pa ) i
ON i.audit_field=''',@pa,''' AND i.A_n=a.A_n AND a.',@pa,'<>i.field_after')
print @cmd -- for debug
exec(@cmd)
FETCH NEXT FROM audit_cur into @pa
END
CLOSE audit_cur
DEALLOCATE audit_cur
You should probably use set based query instead of a cursor and dynamic sql for that, but for a quick and dirty fix you need to change this part where a.Audit_Field=@pa in your dynamic SQL string to where a.Audit_Field='''+ @pa +''' The dynamic sql doesn't know the @pa variable since it runs in it's own scope.
Also, your SQL hints you are using comma delimited values in the audit_field.
That's a really bad thing to do - for more information, read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!
If you want some guidence on how to use a set based query instead of this mess, please either post a new query or edit this one to include sample data as DDL + DML, and desired results.