Must declare scalar variable - using cursors

Question

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

Answer 1

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.