SEO

#1 (**permalink**) 02-29-2008, 06:40 PM

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)

#2 (**permalink**) 02-29-2008, 06:40 PM

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/m...S,SQL.90).aspx
--

#3 (**permalink**) 02-29-2008, 06:40 PM

David,

PERFECT. You hit the nail on the freaking head. THANK YOU so much!!

Take it easy and have a good day!!

- Sean

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Syntax of updating table variables?