SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting variable name from cursor as part of query results


Getting variable name from cursor as part of query results

Author
Message
Gazareth
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3981 Visits: 5798
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.
jbm6401
jbm6401
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 83
So if this is the case, is there any way around the dynamic SQL? Thanks.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25867 Visits: 17509
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 Modens 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)
jbm6401
jbm6401
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 83
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
Stuart Davies
Stuart Davies
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5273 Visits: 4685
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
jbm6401
jbm6401
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 83
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
Gazareth
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3981 Visits: 5798
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search