Getting variable name from cursor as part of query results

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Getting variable name from cursor as part of...

17 posts, Page 2 of 2

««1 2

Getting variable name from cursor as part of query results

Rate Topic

Display Mode

Topic Options

Author

Message

Gazareth

Posted Thursday, November 15, 2012 8:50 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:22 AM
Points: 1,474, Visits: 2,341

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

jbm6401

Posted Monday, November 19, 2012 7:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:47 AM
Points: 9, Visits: 54

So if this is the case, is there any way around the dynamic SQL? Thanks.

Post #1386386

Sean Lange

Posted Monday, November 19, 2012 8:31 AM

SSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547, Visits: 8,204

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

Post #1386428

jbm6401

Posted Monday, November 19, 2012 11:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:47 AM
Points: 9, Visits: 54

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

Stuart Davies

Posted Friday, November 23, 2012 4:34 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:19 AM
Points: 2,473, Visits: 2,131

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

jbm6401

Posted Thursday, December 06, 2012 5:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:47 AM
Points: 9, Visits: 54

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

Gazareth

Posted Thursday, December 06, 2012 6:50 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 6:22 AM
Points: 1,474, Visits: 2,341

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 »

17 posts, Page 2 of 2

««1 2

Permissions