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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Implemented with a few changes.
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/datetim
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"