Solved

executing dynamic query that selects from temp table variable

Posted on 2010-11-09
5
528 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ms sql and asp dates 5 41
Creating a View from a CTE 15 42
SQL Query 9 25
T-SQL: need to reset a declared variable 4 28
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 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