Table name from variable in select statement

Question

Post reply

Table name from variable in select statement

B Hilderman

SSC-Addicted

Points: 435
More actions
December 19, 2007 at 11:30 am

#182150

Here's what I have that creates the temporary table;
REATE TABLE ##Acct_Cursor
( TabNam VARCHAR(60))
Go
Select distinct
SysObjects.Name 'TabNam'
INTO Acct_Cursor
From SysObjects, SysColumns, SysTypes
Where SysObjects.ID = SysColumns.ID
And SysColumns.xType = SysTypes.xType
And SysColumns.Name like '%ACC%'
And SysObjects.xType <> 'V'
ORDER by SysObjects.Name
Go
I then went into sql management studio and created an identity field named recno that is incremented by one. Table is created. I have all the table names I need in that table now...
example data:
acct_2007_inacted 1
acct_2008_inacted 2
chartacc 3
elmer1 4
ESABANKD 5
Here's the rub...
declare @tab varchar(60)
declare @trig int
set @trig = 0
while @trig < 10
begin
set @trig = @trig + 1
set @tab = (select TabNam from Acct_Cursor
where recno = @trig)
select top (5) *
from @tab
end
The FROM @tab does NOT work. Tells me I need to declare a type of variable as table, BUT, I already have the table... So how do I get this to loop through, get me the five records from each table based on the table names in my table Acct_cursor???

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

The table name cannot be variable without using some form of dynamic SQL... kinda like...

DECLARE @TableName SYSNAME

SET @TableName = 'sometablename'

EXEC ('SELECT * FROM ' + @TableName)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

B Hilderman SSC-Addicted Points: 435 More actions · Answer 2

B Hilderman

SSC-Addicted

Points: 435

December 19, 2007 at 12:18 pm

#761513

Yes, of course, thank you!

grtn316 Ten Centuries Points: 1239 More actions · Answer 3

Jeff Moden (12/19/2007)
The table name cannot be variable without using some form of dynamic SQL... kinda like...
DECLARE @TableName SYSNAME
SET @TableName = 'sometablename'
EXEC ('SELECT * FROM ' + @TableName)

Jeff,

I'm having a similar issue. My problem is that I also have a where statement with conditions. Something like:

DECLARE @sql varchar(1000)

DECLARE @TableName SYSNAME

DECLARE @prod_num INT(10)

SET @TableName = 'sometablename'

SET @prod_num = 3

@sql = 'SELECT * FROM ' + @TableName + ' Where prod_id=' + @prod_num

EXEC(@SQL)

I receive an error that says I cannot convert varchar to int.

Any Ideas?

B Hilderman SSC-Addicted Points: 435 More actions · Answer 4

Not sure why you're using int(10) rather than just int.

You have;

DECLARE @prod_num INT(10)

use

DECLARE @prod_num INT

grtn316 Ten Centuries Points: 1239 More actions · Answer 5

B Hilderman (12/19/2007)
Not sure why you're using int(10) rather than just int.
You have;
DECLARE @prod_num INT(10)
use
DECLARE @prod_num INT

Sorry, that was a typo in haste. Any ideas why it would still not work?

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 6

Try this:

SET @sql = 'SELECT * FROM ' + @TableName + ' Where prod_id=' + CAST(@prod_num AS varchar(10))

There is no "i" in team, but idiot has two.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 7

Dave has it the problem head on.

You cannot directly concatenate an integer into a string because SQL will try to convert the string into an integer. To remedy this you must cast or convert the integer into a character type char, varchar, nvarchar etc, then concatenate it with the string, as shown in the example by Dave.

My blog: http://jahaines.blogspot.com

grtn316 Ten Centuries Points: 1239 More actions · Answer 8

OK, Cool. I think I can figure that out. However, after messing with it for a bit, now my declaration for @sql is messed up....

ALTER PROCEDURE [dbo].[procedure_name]

@DateFilter smalldatetime = '01/01/2000',

@FilterType varchar(40)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @sql varchar(1000)

@sql = ''

END

I get an error that says: Incorrect syntax near '@sql'.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 9

Need a set statement

set @sql =

My blog: http://jahaines.blogspot.com

grtn316 Ten Centuries Points: 1239 More actions · Answer 10

grtn316

Ten Centuries

Points: 1239

December 19, 2007 at 3:35 pm

#761616

Fixed:

declare @sql varchar

Adam Haines SSC-Insane Points: 23217 More actions · Answer 11

if you dont specify a length for varchar, it will default to 30.

My blog: http://jahaines.blogspot.com

grtn316 Ten Centuries Points: 1239 More actions · Answer 12

Crap didnt work. Just saw replies, that should work.

Thanks!

B Hilderman SSC-Addicted Points: 435 More actions · Answer 13

I have no problem running this...

set @trig = @trig + 1

EXEC('select TabNam from Acct_Cursor where recno = ' + @trig)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 14

Funny how things work out... the original poster ended up helping a rider 😉 Nicely done, Hilderman!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)