December 19, 2007 at 11:30 am
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???
December 19, 2007 at 12:13 pm
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
Change is inevitable... Change for the better is not.
December 19, 2007 at 12:18 pm
Yes, of course, thank you!
December 19, 2007 at 2:55 pm
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?
December 19, 2007 at 3:05 pm
Not sure why you're using int(10) rather than just int.
You have;
DECLARE @prod_num INT(10)
use
DECLARE @prod_num INT
December 19, 2007 at 3:08 pm
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?
December 19, 2007 at 3:19 pm
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.
December 19, 2007 at 3:24 pm
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'.
December 19, 2007 at 3:35 pm
December 19, 2007 at 3:37 pm
if you dont specify a length for varchar, it will default to 30.
December 19, 2007 at 3:38 pm
Crap didnt work. Just saw replies, that should work.
Thanks!
December 19, 2007 at 3:38 pm
I have no problem running this...
set @trig = @trig + 1
EXEC('select TabNam from Acct_Cursor where recno = ' + @trig)
December 19, 2007 at 4:22 pm
Funny how things work out... the original poster ended up helping a rider Nicely done, Hilderman!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy