Accessing Temp tables from Dynamic SQL EXECTE Stmt

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Accessing Temp tables from Dynamic SQL EXECTE Stmt

Author

Topic

achamarthy
Starting Member

26 Posts

Posted - 2004-07-27 : 17:08:01

For my stored procedure, i created Dynamic sql which will make a select statement and i need to execute that inside a select statement...but it is not allowing me to access...below is my code and at the end i will explain exactly what i did..

********************CODE TO PREPARE DYNAMIC SQL******************

SET @counter = 0

declare Attribute_cursor cursor for select AttributeID from @AttributesTable

open Attribute_cursor
SET @SQL = 'select MISSDE.LotItemID from MISSDenormalized_MPTest MISSDE '
fetch next from Attribute_cursor into @AttributeID
while @@FETCH_STATUS = 0

begin
set @counter = @counter+1
SET @SQL = @SQL + 'inner join '
SET @SQL = @SQL + '@ATTRIBUTESTABLE as Attributes'+ CONVERT(VARCHAR,@counter) +' on '
SET @SQL = @SQL + 'MISSDE.AttributeID'+ CONVERT(VARCHAR,@AttributeID)
SET @SQL = @SQL + ' = Attributes'+ CONVERT(VARCHAR,@counter) +'.AttributeValue and '
SET @SQL = @SQL + 'Attributes'+ CONVERT(VARCHAR,@counter) +'.AttributeID = ' + CONVERT(VARCHAR,@AttributeID)
SET @SQL = @SQL + ' and MISSDE.[DestLat] between '
SET @SQL = @SQL + 'Attributes'+ CONVERT(VARCHAR,@counter) +'.MinLat and ' + 'Attributes'+ CONVERT(VARCHAR,@counter) +'.MaxLat'
SET @SQL = @SQL + ' and MISSDE.[DestLon] between '
SET @SQL = @SQL + 'Attributes'+ CONVERT(VARCHAR,@counter) +'.MinLon and ' + 'Attributes'+ CONVERT(VARCHAR,@counter) +'.MaxLon '

FETCH NEXT FROM Attribute_Cursor into @AttributeID
end
close Attribute_cursor
deallocate Attribute_cursor
********************************************************************
the whoole string is stored in VARCHAR variable @SQL
The result of that is

********************************************************************
select MISSDE.LotItemID from MISSDenormalized_MPTest MISSDE

inner join
@ATTRIBUTESTABLE as Attributes1 on
MISSDE.AttributeID12 = Attributes1.AttributeValue and Attributes1.AttributeID = 12
and MISSDE.[DestLat] between Attributes1.MinLat and Attributes1.MaxLat
and MISSDE.[DestLon] between Attributes1.MinLon and Attributes1.MaxLon

inner join @ATTRIBUTESTABLE as Attributes2 on
MISSDE.AttributeID25 = Attributes2.AttributeValue and Attributes2.AttributeID = 25
and
MISSDE.[DestLat] between Attributes2.MinLat and Attributes2.MaxLat
and MISSDE.[DestLon] between Attributes2.MinLon and Attributes2.MaxLon

inner join
@ATTRIBUTESTABLE as Attributes3 on MISSDE.AttributeID6 = Attributes3.AttributeValue and Attributes3.AttributeID = 6 and
MISSDE.[DestLat] between Attributes3.MinLat and Attributes3.MaxLat
and MISSDE.[DestLon] between Attributes3.MinLon and Attributes3.MaxLon

********************************************************************

when i'm trying to execute this in a select statment..for example

select * from maintable where LotItemId in ( EXEC(@SQL))

i'm getting following errors..

Server: Msg 156, Level 15, State 1, Procedure ap_SupplierSearchSummary_test, Line 966
Incorrect syntax near the keyword 'EXEC'.
Server: Msg 170, Level 15, State 1, Procedure ap_SupplierSearchSummary_test, Line 968
Line 968: Incorrect syntax near ')'.

I tried to seperate the two queries into two strings and concatenated them byt ended up getting the following error...

SET @LINE = 'select * from maintable where LotItemID in '
PRINT @LINE
SET @LINE = @LINE + @SQL
EXEC (@LINE)

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@ATTRIBUTESTABLE'.
select * from maintable where LotItemID in

WHAT IS WRONG I'M DOING?? CANT I ACCESS TEMPORARY TABLES FROM A DYNAMIC SQL USING A EXECUTE FUNCTION???????

Any suggestions??

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-07-27 : 18:05:27

Nope, dynamically executed code has a different "thread context" (which is much like a spid) than its host. That means that local variables, temporary tables, etc are not visible to the dynamic code.

-PatP

achamarthy
Starting Member

26 Posts

Posted - 2004-07-27 : 18:44:47

Is there any workaround for this?? How can i work my issue then? Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-27 : 19:19:47

A cursor with dynamic sql!!! Have you considered how badly this is going to perform regardless if you could get this to work?

Global temporary tables might solve your temp table problem in dynamic sql.

Tara

achamarthy
Starting Member

26 Posts

Posted - 2004-07-27 : 19:25:48

I'm not very good in DB stuff...Am not sure how bad it is to use cursor in dynamic sql !!! I have to make this stored proc perform well...How bad it is to use cursor in dynamic sql??? Is there any work around for that??

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-27 : 19:29:25

Well, cursors are very, very, very bad for performance. Dynamic SQL is also bad but not as bad. But you've got dynamic SQL and cursors. That certainly will not perform well.

Could you explain what you are trying to do? Not what your code is doing, but is the requirement? Show us some sample data, the table definitions, and the expected result set using the sample data and we might be able to come up with a set-based solution (which means no cursors at the very least).

Tara

achamarthy
Starting Member

26 Posts

Posted - 2004-07-27 : 20:45:07

Tara -

We have a main table from where we have to fetch the dataset based on the search data that we have in temp table @ATTRIBUTETABLE..

Descripion of MISSDenormalized_MPTest table
-------------------------------------------

This table is a Dynamic table..The table structure will be changed every night and the AttrubiteXX(01,02...NN) columns may increase or decrease every night...

Columns
-------
CompName, FName,MinLat, MinLon, MaxLat, MaxLon, LotID, AttributeID1,AttributID2,AttributeID3.....,AttributeID12..AttributeID25..etc

Desc of Temp table
-----------------

This table will be filled with the data based on some of the input that the stored procedure gets..

Columns
-------
AttributeID, AttributeValue , MinLat, MinLon, MaxLat, MaxLon

INPUT TO THE STORED PROCEDURE
-----------------------------
This stored procedure get an XML file which has search criteria (Which changes based on selection in web page)....This file has Attribute Nodes which has search values, MinLat, MinLon, MaxLat, MaxLon etc....

All this data is first loaded into the temp table described above...

and depending on the data we have in temp table, dynamic sql is used to create a select statement..
For ex we have 3 values in temp table now (12,25 and 6) and that's why i used cursor and dynamic sql to create select statment

****************************************************************
select MISSDE.LotItemID from MISSDenormalized_MPTest MISSDE

inner join
@ATTRIBUTESTABLE as Attributes1 on
MISSDE.AttributeID12 = Attributes1.AttributeValue and Attributes1.AttributeID = 12
and MISSDE.[DestLat] between Attributes1.MinLat and Attributes1.MaxLat
and MISSDE.[DestLon] between Attributes1.MinLon and Attributes1.MaxLon

inner join @ATTRIBUTESTABLE as Attributes2 on
MISSDE.AttributeID25 = Attributes2.AttributeValue and Attributes2.AttributeID = 25
and
MISSDE.[DestLat] between Attributes2.MinLat and Attributes2.MaxLat
and MISSDE.[DestLon] between Attributes2.MinLon and Attributes2.MaxLon

inner join
@ATTRIBUTESTABLE as Attributes3 on MISSDE.AttributeID6 = Attributes3.AttributeValue and Attributes3.AttributeID = 6 and
MISSDE.[DestLat] between Attributes3.MinLat and Attributes3.MaxLat
and MISSDE.[DestLon] between Attributes3.MinLon and Attributes3.MaxLon

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

MISSDenormalized_MPTEST table data
---------------------------------
Since AttributeXX columns are hundreds, i will specify only three columns for three values

CompName, FName,DestLat, DestLon, AttributeID12,AttributID25,AttributeID6, Lot ID
-----------------------------------------------------------------------------------------------------------------------
ABC, John, 5 11 welding Pump Screw L1

BCD Mary 6 13 King Fur Hang L2

XYZ Tom 12 20 Queen Sword Boring L3

hhh Anil 34 23 red green white L4
Data for Temp table

AttributeID, AttributeValue , MinLat, MinLon, MaxLat, MaxLon
------------------------------------------------------------
12, Welding, 1, 2, 10, 20
25, Fur, 3, 6, 30, 60
6, Boring, 6, 9, 60, 90

When i use the above dynami query that i created on the above data i should get the first three records from the MISSDENORMALISED_MPTEST table with L1, L2 and L3 as lot id's.....I use these Lot ID's to fetch some other data from other table ( I thought that table def is not necessary)....

As you see, since the search criteria changes and so does the temp table content...And becase of that i need to use Cursor to prepare the Dynamic SQL Statement....

Let me know if you need more information...
I want this SP to perform in a better way, so please give me any valuable suggestions..

Thanks a ton in advance...

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-27 : 21:57:58

Your dynamic SQL should work if you move @ATTRIBUTESTABLE outside of the quotes

SET @SQL = @SQL + 'inner join '
SET @SQL = @SQL + @ATTRIBUTESTABLE + ' as Attributes'+ CONVERT(VARCHAR,@counter) +' on ' ---<< Here
SET @SQL = @SQL + 'MISSDE.AttributeID'+ CONVERT(VARCHAR,@AttributeID)
SET @SQL = @SQL + ' = Attributes'+ CONVERT(VARCHAR,@counter) +'.AttributeValue and '
SET @SQL = @SQL + 'Attributes'+ CONVERT(VARCHAR,@counter) +'.AttributeID = ' + CONVERT(VARCHAR,@AttributeID)
SET @SQL = @SQL + ' and MISSDE.[DestLat] between '
SET @SQL = @SQL + 'Attributes'+ CONVERT(VARCHAR,@counter) +'.MinLat and ' + 'Attributes'+ CONVERT(VARCHAR,@counter) +'.MaxLat'
SET @SQL = @SQL + ' and MISSDE.[DestLon] between '
SET @SQL = @SQL + 'Attributes'+ CONVERT(VARCHAR,@counter) +'.MinLon and ' + 'Attributes'+ CONVERT(VARCHAR,@counter) +'.MaxLon '

select * from maintable where LotItemId in ( EXEC(@SQL))

Will not work. You will have to use:

SET @SQL = 'select * from maintable where LotItemID in (' + @SQL + ')'
EXEC (@SQL)

You probably want indexes on EVERY AtributeNN column of your MISSDenormalized_MPTEST table. Since the table is built nightly, build the indexes AFTER you create the table.

I would question weather it is not possible to query the normalized tables to begin with. The dynamic SQL you are building will only support about 27 attribute column search criteria. What is the MISSDenormalized_MPTEST table built from?

--Ken
Your Kung-Fu is not strong. -- 'The Core'

achamarthy
Starting Member

26 Posts

Posted - 2004-07-27 : 22:34:42

Why is that its 27 times? Is it a limitation by SQL Server?

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-27 : 23:03:39

It is the 8000 character limit of varchar datatype. 27 * 273 (the length of 1 inner join clause) is about 8000.

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources