Solved

executing dynamic query that selects from temp table variable

Posted on 2010-11-09
5
527 Views
Last Modified: 2012-05-10
I am executing a dynamic query that is joining to a temp variable.
EXECUTE sp_executesql @strSQLAtt

I get the error:
Must declare the table variable "@ItemTemp".


But when I execute the query that is formed in the string @strSQLatt listed below works. Please suggest how I can achieve this. I need to be able to build portion of the query dynamically, but at the same time be able to join to a temp table. Any suggestions would be great. Thanks

SELECT AN.ShortName, AV.AttributeValue, A.AttributeValueID,
            AV.AttributeNameID, COUNT(*) AS ATTRIBUTECOUNT FROM Attributes A
            INNER JOIN @ItemTemp I ON      I.ITEMID = A.ItemID
            INNER JOIN AttributeValues AV ON AV.ID = A.AttributeValueID
            INNER JOIN AttributeNames AN ON AN.ID = AV.AttributeNameID GROUP BY A.AttributeValueID, AN.ShortName, AV.AttributeValue, AV.AttributeNameID
0
Comment
Question by:TrialUser
5 Comments
 
LVL 7

Accepted Solution

by:
mkobrin earned 125 total points
ID: 34099295
I would name the temp table #ItemTemp, the @symbol is reserved for variables and the # symbols is usually used for temp tables
0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 125 total points
ID: 34099541
Hi,

When you run dynamic query then only that part will run irrespective of your above code.

e.g.

DECLARE @TABLE TABLE (
COL LIST)

SELECT * FROM @TABLE - THIS WILL WORK

SET @strSQLAtt = 'SELECT * FROM @TABLE'

EXECUTE sp_executesql @strSQLAtt - THIS WILL WONT WORK BECAUSE YOU DECLARE TABLE OUTSIDE.AS MKOBRIN SAID,EITHER YOU CAN CREATE #TABLE OR YOU CAN PUT @TABLE CODE INSIDE @strSQLAtt

However,dynamic query is used for passing some value dynamically.
You no need to use dynamic query for basic kind of query.

Or explain your requirment so experts can guide you properly
0
 
LVL 6

Assisted Solution

by:subhashpunia
subhashpunia earned 125 total points
ID: 34100002
Local temp tables do not work in dynamic statments. Because temp table (#) are available only in the current session but dynamic statements are executed in different session.
You know global temp table (##) is available to all session so also in dynamic query. So please change the temp table or table variable with global temp tables and your scriptshould run fine.
0
 
LVL 18

Assisted Solution

by:deighton
deighton earned 125 total points
ID: 34100947
You cant' pass table variables to stored procedures, you are using table variables here not temporary tables, am I right?

About limitations of table variables
http://odetocode.com/Articles/365.aspx

with temporary tables e.g

select * INTO #MyTempTable FROM ATable

you can create and populate #MyTempTable in a stored procedure, then call a stored procedure and use #MyTempTable.  You can use it dynamically if you wish

not that ##temp tables become available to all sessions, and are risky!

see this simple example, it accepts a temporary table name from a procedure that created the temporary table (e.g. #temp), then selects from it dynamically

CREATE PROCEDURE [dbo].[a2]
    -- Add the parameters for the stored procedure here
    @TAB varchar(20)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @DYNAMIC varchar(100);
    SET @DYNAMIC = 'select * from ' + @TAB;
   
    EXEC (@DYNAMIC);

END
0
 

Author Closing Comment

by:TrialUser
ID: 34111220
Thanks for all suggestions. I moved from table variable to temp table.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question