Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

executing dynamic query that selects from temp table variable

Posted on 2010-11-09
5
Medium Priority
?
531 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 500 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 500 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 500 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 500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

610 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