Referencing temporary table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Referencing temporary table

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-10 : 08:23:42

I seem to have a problem referenceing my temporary table:

CREATE PROCEDURE SearchAttributes

As

DECLARE @NewTable TABLE (productid nvarchar(50), valueforType1 int, valueForType2 int)

INSERT INTO @NewTable (productid)

SELECT p.productID FROM ProductAttributeValues p

UPDATE @NewTable

SET ValueForType1 = (Select pat.attributeValueID

FROM
ProductAttributeValues pat
INNER JOIN
AttributeValues av on pat.AttributeValueID = av.AttributeValueID where av.attributeTypeID = 1 )

where exists
(select pat1.ProductID from ProductAttributeValues where pat1.productid = @NewTable.ProductID)

Go

I get the error: Must declare the variable @NewTable

Any ideas please?? Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-10 : 08:28:12

>> (select pat1.ProductID from ProductAttributeValues where pat1.productid = @NewTable.ProductID)
(select x.ProductID from ProductAttributeValues x inner join @NewTable y on x.ProductID = y.ProductID)

----------------------------------
'KH'
everything that has a beginning has an end

jimmy_a
Starting Member

38 Posts

Posted - 2006-02-10 : 08:33:10

(select pat1.ProductID
from ProductAttributeValues A, @NewTable B where A.productid = B.ProductID)

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-10 : 09:21:12

Thanks for the replies. I have resolved the problem, but now when I run this SP, I get this error:
"
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
"

My SP is :
CREATE PROCEDURE SearchAttributesTable

As

DECLARE @NewTable TABLE (productid nvarchar(50), valueforType1 int, valueForType2 int)

INSERT INTO @NewTable (productid)
SELECT distinct p.productID
FROM ProductAttributeValues p

UPDATE @NewTable

SET ValueForType1 = (Select pat.attributeValueID
FROM
ProductAttributeValues pat
INNER JOIN
AttributeValues av on pat.AttributeValueID = av.AttributeValueID where av.attributeTypeID = 3)

where exists (select tb.productid from @NewTable tb, ProductAttributeValues pat1 where tb.productid = pat1.productid)

SELECT * FROM @NewTable
GO

Basically, @NewTable holds 4 rows. For each row, I want to update the AttributeValueID where the @NewTable.ProductID is same as
ProductAttributes.ProductID

What could I be doing wrong. Regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-10 : 09:23:59

The General approach is

Update T1
set col=T2.col
from table1 T1 inner join table2 T2
on T1.keycol=T2.keycol

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-10 : 09:25:51

Try this :

UPDATE 	nt
	SET	nt.ValueForType1 	= pa.attributeValueID
FROM	@NewTable nt INNER JOIN ProductAttributeValues pa
	ON	nt.productid		= pa.productid
	INNER JOIN AttributeValues av
	ON	pa.AttributeValueID	= av.AttributeValueID
WHERE	av.attributeTypeID		= 3

----------------------------------
'KH'
everything that has a beginning has an end

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-02-10 : 09:34:36

Thank guys. I spent a lot of time on this SP :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-10 : 09:37:40

>> I spent a lot of time on this SP :)

But Tan made you to leave rest of the time