Query a dynamic table...HELP!

[jshull]

Hello,

I am trying to write a query to pull data from a table that is system generated daily at midnight. I tried:

declare @tablename varchar(50) set @tablename = (select name from sysobjects where crdate = '05/01/2003') select * from @tablename

But I get an error on 'select * from @tablename' (must declare @tablename)

Any assistance is GREATLY APPRECIATED!!!!

[Claire]

(1)
According to your original post.It seems you only create one table per day.
Because if you have more than one table.You will get a error (subquery cant have multiple returns)

----------------------------------------
(2)here is the modified query
Instead of 50 ,put 128 instead!

declare @tablename varchar(128) set @tablename = (
select name from sysobjects where left(crdate,10) = left(convert(datetime,'11/13/1998'),10)
)

---------------------------------------
(3)You can run this to see the result first.If you have many table name returns,you have to put the result into a table and use loop or cursor to retrive row by row

select name from sysobjects where left(crdate,10) =
left(convert(datetime,'11/13/1998'),10)

[MAK]

if it is only one table
------------------------
declare @tablename varchar(128)
set @tablename = (select top 1 name from sysobjects where convert(varchar(8),crdate,112) = '20030501' and type ='u')
exec ('select * from '+ @tablename )