variable tables - 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 2005 Forums

Transact-SQL (2005)

variable tables

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-02 : 07:11:22

hi
i have sql with a variable trable and want to test the sql
what do i do to get it to work

i tried declare @tbl int
but it didnt work any ideas

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-02 : 07:13:06

Have you looked at sql server help for the syntax of declaring table variable?

Harsh Athalye
http://www.letsgeek.net/

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-02 : 07:21:10

ya it says to do what im doing but does not seem to work.
has anyone ever done something like this

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-02 : 07:23:26

declare @tbl table(i int)
insert into @tbl(i) values(12)

select i from @tbl

Madhivanan

Failing to plan is Planning to fail

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2010-02-02 : 07:26:55

this is code i want to test said i would put it up might help


SELECT DISTINCT
		am.AddressID	,
		am.AddressText	,
		am.CountryID
FROM	@tbl t
INNER
JOIN	Harvest.dbo.AddressMap am
ON		am.AddressText = t.AddressText
WHERE	am.CountryID	=	1

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-02-02 : 08:45:31

Where is the data going into @tbl coming from?

To set up that table you can do...

Declare @tbl table ( addressID int, addressText Varchar(max), countryID int)
Insert @tbl
Select 1, 'address', 100 Union All
Select 2, 'address2', 101 Union All
......
Select 100, 'address100', 200

Or use a select statement to get your info to put into @tbl.

Note: This follows exactly what Madiv was telling you to do, my question is more of where are you getting the data to stuff into @tbl??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 09:35:23

you need to make sure you run batch including declare @table part together . running your select alone throws error as table variable will be out of scope

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources