?
Solved

executing dynamic query that selects from temp table variable

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

850 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