Dynamic Table Name creation...

Hi there,

I am trying to generate tables names on the fly depending on another table. So i am creating a local variable containing the table names as required. I am storing the tables in a local variable called

@.TABLENAME VARCHAR(16)

and when i say SELECT * FROM @.TABLENAME

it is giving me an error and I think I cannot declare @.TABLENAME as a table variable because I do not want to create a temp table of sorts.

I hope I am clear.

thanks,

Murthy here

can you post the error generated by SQL server

and the code for generation of tables names , I am curious to see how we can achieve it :)

|||

Are you looking for something like this:

Declare @.tempTable table (
TableName varchar(16)
)

Insert into @.tempTable
Select 'Apples'
union
Select 'Pears'

Select * from @.tempTable

Results:
TableName
------
Apples
Pears

|||

Hi there,

I am posting a portion of my code:

SET @.TABLENAME = 'FISCURRENT.dbo.DC67TRANSCYR' + '0' + CAST((CAST((SUBSTRING(@.FISCALYEAR1,3,2)) AS INT) - 1) AS VARCHAR) + SUBSTRING(@.FISCALYEAR1,3,2)

SELECT DESCRIPTION, TRANSTYPE , BE, CATEGORY, FID , OCA, '67' + ORGL2L5 , EO, AMOUNT, GL, TRANSDATE, MACHINEDATE, 'N' AS BMS, THEYEAR
FROM @.TABLENAME
WHERE ((TRANSTYPE = '20') AND (GL = '92200')) OR
((TRANSTYPE = '21') AND (GL IN ('91100', '92100'))) OR
((TRANSTYPE = '22') AND (GL IN ('13100', '12200'))) AND (BE IN (SELECT DISTINCT BE FROM REF_BUDGETENTITY WHERE FISCALYEAR = @.FISCALYEAR))
And the error message is :

Must declare the table variable "@.TABLENAME".

thanks,

Murthy here

|||

Hi there,

I do not have to create any temp tables. Actually the tables already exist with sql server. based on 1 field from 1 table I have to select data from 1 or more different tables.

The select statement has to different for different tables so I am generating the table names which already exist on the fly.

Hope I am clear.

thanks,

Murthy here

|||

I think you will need to use sp_execsql to execute the dynamic sql you build that way

Keep in mind that many people discourage the use of dynamic sql because of security and performance

1. SQL injection attacks

2. Having to compile the statement every time

3. YOu need to grant permissions on the underlying tables, not on the proc

|||

If you are trying to do something like this:

Declare @.table varchar(20)
set @.table = Northwind.dbo.Products

Select * from @.table

You can't. It's looking for a table afterfromso @.table would have to be a table variable.

To do what you need, asdbland07666 mentioned, you need to use dynamic sql. Caveat Emptor. Here is a link that might help you:

http://www.nigelrivett.net/SQLTsql/TableNameAsVariable.html