SQL - Table variable in "where" clause

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)

SQL - Table variable in "where" clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-12-18 : 08:24:58

adam writes "Are you allowed to use table variables in a where clause?
In the example below it accepts TxE.EmployerID (because i am using an alias "TxE"?) BUT rejects @TaxCert.EmployerID with the
message "Must declare the variable '@TaxCert'.
am i doing something stupid? - please help!

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 service pack 2

UPDATE @TaxCert -- @TaxCert is table variable
SET
Code4472 = TxE.Code4472,
Code4472Nam = TxE.Code4472Nam,
Code4472Val = TxE.Code4472Val,

FROM @TxCERCont TxE -- @TaxCerCont is also a table variable
WHERE @TaxCert.EmployerID = TxE.EmployerID"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-18 : 08:30:49

When you define alias for table (TxE in this case), subsequent reference to the table should be done using alias only. You can't alternate between using Alias one time and table name other time.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-18 : 08:32:04

UPDATE a
SET
Code4472 = TxE.Code4472,
Code4472Nam = TxE.Code4472Nam,
Code4472Val = TxE.Code4472Val,
FROM @TaxCert a
INNER JOIN @TxCERCont TxE
ON a.EmployerID = TxE.EmployerID

Duane.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 08:34:39

[code]UPDATE x
SET x.Code4472 = TxE.Code4472,
x.Code4472Nam = TxE.Code4472Nam,
x.Code4472Val = TxE.Code4472Val
FROM @TaxCert x
INNER JOIN @TxCERCont TxE ON x.EmployerID = TxE.EmployerID[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 08:35:18

Peter Larsson
Helsingborg, Sweden

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-12-18 : 08:40:10

Mine is just not as neatly laid out as yours - That's probably how I managed to Snipe you

Duane.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 08:50:37

Well, it's the thought that's count, right?
I just wasted time on some fancy smancy formatting...

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-18 : 09:43:01

<<
"Are you allowed to use table variables in a where clause?
>>

No you cant directly use table variable as prefix to the column. You should use alias name instead. Also you should know how to use ANSI joins

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-12-18 : 10:13:57

Do your joins in the front end!!!

oh wait, never mind

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources