Posted by David Portas on 03/10/06 20:08

sean.gilbertson@gmail.com wrote:
> Hi,
>
> I have a user-defined function which returns a table (call it '@a'),
> and has another table defined as a variable (call it '@b'). When I try
> to do the following query, I get "Must declare the variable '@b'" and
> "Must declare the variable '@a'." How do I remedy this?
>
> The query:
>
> UPDATE @a
> SET
> stuff =
> (SELECT otherStuff From @b
> WHERE @b.someID = @a.someID)

Have you declared your table variables? Assuming you have you still
need to use an alias. Variable names aren't valid as aliases:

DECLARE @a TABLE (someid INT, stuff INT);
DECLARE @b TABLE (someid INT, otherstuff INT);

UPDATE A
SET stuff =
(SELECT B.otherstuff
FROM @b AS B
WHERE B.someID = A.someID)
FROM @a AS A ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

• Next in forum: Re: Syntax of updating table variables?
• Prev in forum: Re: Different query plans for view and view definition statement
• Thread view: Re: Syntax of updating table variables?

[Reply to this message]