executing dynamic query that selects from temp table variable
Posted on 2010-11-09
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