Getting variable name from cursor as part of query results

• jbm6401

  Mr or Mrs. 500

  Points: 513

  More actions

  December 6, 2012 at 5:47 am

  #1566120

  I tried creating a table to do the same thing, but got the same error.

  I need to populate the table, and then view the results.

  Any help is appreciated.

  Thanks,

  Joe

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  December 6, 2012 at 6:50 am

  #1566142

  Hi Joe,

  Your problem is #tmptbl2 is only present in the scope of the SQL executed via EXEC (@SQL1-4), and is out of scope when you try to select from it.

  You can create the temp table outside this & it will be available to the dynamic & fixed SQL.

  Something like this:

  IF object_id('tempdb..#tmptbl1') Is not null

  drop table #tmptbl1

  IF object_id('tempdb..#tmptbl2') Is not null

  drop table #tmptbl2

  create table #tmptbl2 (table_name varchar(100), AO_Counts INT, AE_Counts INT...

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

  Rewrite your Dynamic bits to:

  Select @sql = '

  insert into #tmptbl2 (table_name, AO_Counts)

  select '''+@tablename+''' as table_name, count (*) as AO_Counts

  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'')'

  Select @SQL2 = '

  insert into #tmptbl2 (table_name, AE_Counts)

  select '''+@tablename+''' as table_name, count (*) as AE_Counts

  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'')'

  etc

  Then at the end, select * from #tmptbl2 should return results.

  Hope that makes sense & note I've split AO, AE counts into seperate fields. You'll need to rework this to the format you require.

  Thanks

  Gaz