Using table variables in subqueries

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)

Using table variables in subqueries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-04 : 08:24:46

Neil writes "Hello I am using two table variables to sequence a set of nodes in a stored procedure and I get errors trying to use one table in a subquery to update the other I have tried a number of examples but I get variable undefined errors

Here are two examples

-- the first gives an error on the SET "incorrect syntax near ".""

UPDATE @orderTable
SET @orderTable.node = @nodeTable.node
FROM @nodeTable
WHERE @orderTable.sequenceNumber = @nodeTable.ID

-- the second has the same error as the first and also "must declare the variable @orderTable"

UPDATE @orderTable
SET @orderTable.node = (SELECT n.node
FROM @nodeTable n
WHERE @orderTable.ID = n.ID)

any ideas?

Thanks,
Neil Davidson
davidsonn@saccounty.net

--------------------------------------
below is the sql preceding the problem
--------------------------------------

-- declare a holding table and insert all folders under the parent folder by current node order

DECLARE @orderTable table
(
ID int identity (100,1),
hierarchyRecID int,
folderRecID int,
sequenceNumber int,
node int
)

-- adding node info for sequenceNumber to make the insert happy, will be replaced with actual sequence further down

INSERT INTO @orderTable
SELECT hierarchyRecID, folderRecID, node, node
FROM FolderHierarchy
WHERE parentFolderRecID = @parentFolderRecID

-- add a sequence number, used because one cannot update identity fields

declare @intCounter int
set @intCounter = 0
update @orderTable
SET @intCounter = sequenceNumber = @intCounter + 1

-- sequence update statements go here

DECLARE @nodeTable table
(
ID int IDENTITY (100,1),
node int
)

INSERT INTO @nodeTable
SELECT node
FROM FolderHierarchy
WHERE parentFolderRecID = @parentFolderRecID
ORDER BY node"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-04 : 08:40:25

try using an alias for the table variable. I have found it is necessary to do that in many cases to avoid errors.

i.e., instead of

select @t.a
from @t

try

select t.a
from @t t

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources