Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Getting variable name from cursor as part of query results Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 8:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
If I read it right, you're executing dynamic SQL (hence the @SQL variable)?
Your temp table will be out of scope from within that execution - hence the error you're getting.
You may experience the same issue using a while loop if you're still using dynamic SQL.
Post #1385219
Posted Monday, November 19, 2012 7:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 11:49 AM
Points: 18, Visits: 80
So if this is the case, is there any way around the dynamic SQL? Thanks.
Post #1386386
Posted Monday, November 19, 2012 8:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 12,034, Visits: 11,062
jbm6401 (11/19/2012)
So if this is the case, is there any way around the dynamic SQL? Thanks.


The way around it is to get rid of the cursor. We can't help you because you "can't" post the code. You have 3 very knowledgeable people plus myself willing to help but we have no details to work with.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1386428
Posted Monday, November 19, 2012 11:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 11:49 AM
Points: 18, Visits: 80
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
Post #1386521
Posted Friday, November 23, 2012 4:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:12 AM
Points: 2,835, Visits: 2,853
Jumping in late on this party - I agree entirely with not using cursors loops etc.

A "quick and dirty" solution would be to create the tables as permanent table not in tempdb as # tables at the beginning of your code, and drop them at the end.
This way they would be visible to to the dynamic SQL.
But the others are right - not the most efficient way forwards.


-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Post #1388117
Posted Thursday, December 06, 2012 5:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 11:49 AM
Points: 18, Visits: 80
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
Post #1393462
Posted Thursday, December 06, 2012 6:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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
Post #1393489
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse