Here it is. Thanks for helping:
IF object_id('tempdb..#tmptbl1') Is not null
drop table #tmptbl1
IF object_id('tempdb..#tmptbl2') Is not null
drop table #tmptbl2
-- load yes information into temp tbl
select ltrim(rtrim(table_name))as table_name,
case when AO = 'Y' Then 'AO' end as AO,
case when AE = 'Y' Then 'AE' end as AE,
case when AR = 'Y' Then 'AR' end as AR,
case when NG = 'Y' Then 'NG' end as NG
into #tmptbl1
from stage.dbo.itapdb_ctrl_t
where AO = 'Y' or AE = 'Y' or AR = 'Y' or NG = 'Y'
--select * from #tmptbl1 t
Declare @tablename varchar(500),
@AO varchar(2),
@AE varchar(2),
@AR varchar(2),
@NG varchar(2),
@SQL varchar (500),
@SQL2 varchar (500),
@SQL3 varchar (500),
@SQL4 varchar (500),
@tbl_counts varchar (500),
@total_tbl_Count varchar(500);
DECLARE table_cursor CURSOR FOR
SELECT
t.table_name, t.ao, t.ae, t.ar, t.ng
FROM
#tmptbl1 AS t
--WHERE
--t.ao = 'Y' or t.ae = 'Y' or t.ar = 'Y' or t.ng = 'Y'
ORDER BY
t.table_name
Open table_cursor;
FETCH NEXT FROM table_cursor
INTO @tablename, @AO, @AE, @AR, @NG;
WHILE @@FETCH_STATUS = 0
Begin
Print @tablename
Print @AO
Print @AE
Print @AR
Print @NG
If @AO = 'AO' begin
Select @SQL = '
select '''+@tablename+''' as table_name, count (*) as AO_Counts
into #tmptbl2
FROM pre_itap.dbo.'+@tablename+' where ssn in (select a.ssn
from pre_itap.dbo.person_t a, pre_itap.dbo.soldr_t b
where a.total_army_comp_cd = ''A''
and b.mil_pers_clas_cd = ''O'')'
Print @SQL
end
If @AE = 'AE' begin
Select @SQL2 = 'select '''+@tablename+''' as table_name, count (*) as AE_Counts
into #tmptbl2
FROM pre_itap.dbo.'+@tablename+' where ssn in (select a.ssn
from pre_itap.dbo.person_t a, pre_itap.dbo.soldr_t b
where a.total_army_comp_cd = ''A''
and b.mil_pers_clas_cd = ''E'')'
end
Print @SQL2
IF @AR = 'AR' begin
Select @SQL3 = 'select '''+@tablename+''' as table_name, count (*) as AR_Counts
into #tmptbl2
FROM pre_itap.dbo.'+@tablename+' where ssn in (select a.ssn
from pre_itap.dbo.person_t a, pre_itap.dbo.soldr_t b
where a.total_army_comp_cd = ''V''
and b.mil_pers_clas_cd in (''E'',''O'',''W''))'
end
Print @SQL3
If @NG = 'NG' begin
Select @SQL4 = 'select '''+@tablename+''' as table_name, count (*) as NG_Counts
into #tmptbl2
FROM pre_itap.dbo.'+@tablename+' where ssn in (select a.ssn
from pre_itap.dbo.person_t a, pre_itap.dbo.soldr_t b
where a.total_army_comp_cd = ''G''
and b.mil_pers_clas_cd in (''E'',''O'',''W''))'
end
Print @SQL4
exec (@SQL);
exec (@SQL2);
exec (@SQL3);
exec (@SQL4)
-- Clear out SQL statements for next iteration
Select @SQL = ' '
Select @SQL2 = ' '
Select @SQL3 = ' '
Select @SQL4 = ' '
FETCH NEXT FROM table_cursor
INTO @tablename, @AO, @AE, @AR, @NG
END
CLOSE table_cursor
DEALLOCATE table_cursor;
select * from #tmptbl2
GO