SQL Select with Table Name as VAR

I am trying to use a variable as a table name in this procedure.  The problems is I can not store it in a another variable and execute it because of the items in the Where statement.

SELECT round(Avg([TSValue]),3)
      FROM @TableName
      where SampleID = @SampleID and TSValue > 0 and (TSDateTime between @StartDateTime and @EndDateTime)

I have attempted to store the entire select in a VAR and run it using EXEC(@ProcedureName) but it did not work.

Any ideas?
LVL 13
luc_roySystem AdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
the trick is to execute the whole thing as a dynamic sql:

create procedure DynSqlExec
@tablename varchar(100),
@sampleid int,
@startTime datetime,
@endtime datetime
as

declare @sql varchar(8000)

--populate @sql with a sql statement plugging in the variables where needed.
set @sql =
'SELECT round(Avg([TSValue]),3)
      FROM ' +  @TableName +
     ' where SampleID = ' + cast(@SampleID as varchar(100) + ' and TSValue > 0 and (TSDateTime between ''' + cast(@StartDateTime as varchar(20)) + ''' and ''' + cast(@EndDateTime as varchar(20)) + ''')'

exec (@sql)



then call like:
exec DynSqlExec  '<TABLE>', <sampleID>, '1/1/2013', '12/31/2013'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
I'd just like to add these notes:

don't leave the conversion of a datetime to defaults, and I'd urge you to use YYYYMMDD i.e.
convert(varchar, @StartDateTime ,112)
convert(varchar, @EndDateTime ,112)

nb: assuming you do not need "time" other than 00:00:00 (if you do then styles 120 or 121)

& I'd like to caution you about using 'between ... and ...' for date ranges
a more reliable method that works for date/smalldatetime/datetime/datetime is:

and ( TSDateTime >= @StartDateTime and TSDateTime < dateadd(day,1,@EndDateTime) )

again assuming selection by day, but the technique works for smaller units too.
please see:  "Beware of Between"
0
luc_roySystem AdminAuthor Commented:
Implemented with a few changes.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.