executing dynamic query that selects from temp table variable

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
TrialUserAsked:
Who is Participating?
 
mkobrinCommented:
I would name the temp table #ItemTemp, the @symbol is reserved for variables and the # symbols is usually used for temp tables
0
 
Bhavesh ShahLead AnalysistCommented:
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
 
subhashpuniaCommented:
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
 
deightonprogCommented:
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
 
TrialUserAuthor Commented:
Thanks for all suggestions. I moved from table variable to temp table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.