declare a variable as a table name

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

Transact-SQL (2000)

declare a variable as a table name

Author

Topic

mixedupone3
Starting Member

8 Posts

Posted - 2002-03-07 : 10:12:01

Is there a way to declare a variable, and then define the variable as a table name?
EX:
declare @tabl varchar
set @tabl = user_addr

select * from @tabl

Nazim
A custom title

1408 Posts

Posted - 2002-03-07 : 10:19:03

if you are Using Sql 2000.

you can declare a variable of type Table.

Check BOL for more information on it.

--------------------------------------------------------------

mixedupone3
Starting Member

8 Posts

Posted - 2002-03-07 : 11:05:33

Whether I use a variable table or any variable, I am having problems using the variable as a table name in the select statement
select * from @tabl

I am running a loop, and I need to pull data from a different table each time. So I need the table name to be a variable, but represent a real table in the database.

I am using SQL 2000

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-07 : 11:14:09

Probably going to have to use dynamic SQL. Here is a sample that may help:

DECLARE @TABLE_NAME VARCHAR(50)

IF @CONDITION1 = 'A'
BEGIN
SET @TABLE_NAME = 'TABLE1'
END
IF @CONDITION2 = 'B'
BEGIN
SET @TABLE_NAME = 'TABLE2'
END
etc...

EXEC('SELECT COLUMN1, COLUMN2 FROM ' + @TABLE_NAME + ' WHERE COLUMN1 = XYZ'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-07 : 11:14:22

Take a look at Merkin's Dynamic SQL articles, he has examples of how to accomplish this:

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

SNIPED AGAIN!

Edited by - robvolk on 03/07/2002 11:14:55

mixedupone3
Starting Member

8 Posts

Posted - 2002-03-08 : 09:23:26

Appreciate all the input! That dynamic SQL looks like some pretty cool stuff, but I wasn't able to use it. Should be useful in the future.

My query was too complex.
select * from TABLE, where field = value would have been easy enough.

But I had:

declare @a int
select @a = max(len(number)) from TABLE
while @a > 1
begin
update #temprate
set #temprate.[nc_rate] = (select rate from TABLE
where ((left(#temprate.[dialed_number],@a)) = TABLE.[number]))
where #temprate.[nc_rate] is null
set @a = (@a - 1)
end

and #temprate.dialed_number was to determine which table 'TABLE' was going to be.

--------------------------------------------------------------

I found my solution by creating a second temp table that combined all of the tables needed, and setting a marker on one of the fields in the temp table so I could identify which table the data was from in the temp table. So now 'TABLE' in the query is a static value.

Thanks again.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-03-08 : 13:35:01

mixedupone3,
If you are seeing that the #temptable is too slow, you could use the suggested Table Variables in SQL 2000. They work similar to #temptables, but they are totally in memory, and don't write to TempDB. Thus, they are hella fast in doing stuff, but do take up some memory while they are in existance. The table variable tables go away at the end of the statement, so the memory they use gets put back when they are destroyed.

I've used them in the past as a replacement for temp tables, and I've been more than pleased with the performance. Just be careful that you don't put a whole lot of data into them, because you'll use up much RAM, and possibly slow your whole system down. Just be careful :)

Michael

mixedupone3
Starting Member

8 Posts

Posted - 2002-03-08 : 14:25:39

I've tried to use a table as a variable, but getting an error.

1 declare @master_rate table
2 (number varchar(50),
3 rate money)
4
5 insert into @master_rate
6 select number,A_011 from A_00_master
7
8 declare @a int
9 select @a = max(len(number)) from @master_rate
10 while @a > 1
11 begin
12 update #temprate
13 set #temprate.[nc_rate] = (select rate from @master_rate
14 where ((left(#temprate.[dialed_number],@a)) = @master_rate.[number]))
15 where #temprate.[nc_rate] is null
16 set @a = (@a - 1)
17 end

getting error:

Server: Msg 137, Level 15, State 2, Line 13
Must declare the variable '@master_rate'.

It is OK with line 9, but line 13 has a problem. (Can't use a local variable inside of the while loop???)

Anyone know how to get around this?

Edited by - mixedupone3 on 03/08/2002 14:26:51

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-03-08 : 16:13:16

That didn't work, but replacing the table variable with a temp table works?

That's odd. I'm at a loss....

Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources