Table name from variable in select statement
-
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???
-
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) -
Yes, of course, thank you!
-
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?
-
Not sure why you're using int(10) rather than just int.
You have;
DECLARE @prod_num INT(10)
use
DECLARE @prod_num INT
-
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?
-
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. -
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.
-
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'.
-
-
Fixed:
declare @sql varchar
-
if you dont specify a length for varchar, it will default to 30.
-
Crap didnt work. Just saw replies, that should work.
Thanks!
-
I have no problem running this...
set @trig = @trig + 1
EXEC('select TabNam from Acct_Cursor where recno = ' + @trig)
-
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)
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply