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
Who is Participating?
Kyle AbrahamsConnect With a Mentor Senior .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

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'
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"
luc_royAuthor Commented:
Implemented with a few changes.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.