Regarding cursors in T-SQL - how to make the column name as field name

Question

if my tables are not clear, I am trying to find differences between a normal table and an audit table.

Iam_audit:

A_n   L_name       A_status   
---------------------------
1     abc          AL       
2     def          AC       

Iam:

a_n   audit_field        field_after    
-------------------------------------
1     L_name             abd    
1     a_status           AL 
2     L_name             def    
2     a_status           AD 

Expected:

a_n   field_after   audit_field   a_n   name
--------------------------------------------
1      abc           l_name        1    abd

a_n   field_after   audit_field   a_n   name
2      AC            a_status      2    AD

Current:

a_n   field_after   audit_field   a_n   name
-----------------------------------------------
1     abc           l_name        1     l_name
2     def           l_name        1     l_name

a_n   field_after   audit_field   a_n   name
1     AL            a_status      1     a_status
2     AC            a_status      2     a_status

The problem is in the iam_audit it is a column name and in Iam it is a column itself. I have lot more columns to compare thatsy using cursor. Any solution?

(check my image attached for clear picture)

declare @af varchar(500)
set @af = 'L_Name,A_Status'

declare @pa varchar(50)

declare audit_cur cursor for
     select ltrim(rtrim(Parameter)) 'Audit_FLD' 
     from phngeneral.dbo.fcn_TheDelimiterEliminator(@af,',')

OPEN audit_cur
FETCH NEXT FROM audit_cur into @pa

WHILE @@FETCH_STATUS = 0
BEGIN
    select *
    from
        (select  a.A_n, a.field_after, audit_field
         from Db.iam_audit a
         where a.Audit_Field = @af) i
    left join
        (select distinct a_n, @pa as name
         from Db.iam) il on i.a_n = il.a_n 
    where 
        i.audittable_field_after <> il.name
    order by 
        1 desc

    FETCH NEXT FROM audit_cur into @pa
END

CLOSE audit_cur
DEALLOCATE audit_cur

Answer 1

I also think you can use dynamic SQL as HABO said.

You need to generate and execute the following queries

SELECT a.A_n,i.audit_field,a.L_name field_value,i.field_after
FROM Iam_audit a
JOIN Iam i ON i.audit_field='L_name' AND i.A_n=a.A_n AND a.L_name<>i.field_after

SELECT a.A_n,i.audit_field,a.A_status field_value,i.field_after
FROM Iam_audit a
JOIN Iam i ON i.audit_field='A_status' AND i.A_n=a.A_n AND a.A_status<>i.field_after

Demo tables and data

create table Iam_audit(A_n int,L_name varchar(10),A_status varchar(10))

insert Iam_audit(A_n,L_name,A_status)values
(1,'abc','AL'),
(2,'def','AC')

create table Iam(a_n int,audit_field varchar(10),field_after varchar(10))

insert Iam(a_n,audit_field,field_after)values
(1,'L_name','abd'),
(1,'a_status','AL'),
(2,'L_name','def'),
(2,'a_status','AD')

And final solution

declare @af varchar(500) = 'L_Name,A_Status'

declare @pa varchar(50)

-- I think you can replace it to your cursor version with fcn_TheDelimiterEliminator
declare audit_cur cursor for
     select distinct audit_field
     from Iam
     where concat(',',@af,',') like concat('%,',audit_field,',%')

/*
declare audit_cur cursor for
     select ltrim(rtrim(Parameter)) 'Audit_FLD' 
     from phngeneral.dbo.fcn_TheDelimiterEliminator(@af,',')
*/

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 Iam 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

Don't forget to add Db. prefix everywhere.

And I think you have confused the names of the tables Iam_audit and Iam in your example. Perhaps you'll need to exchange their names.