The following code works perfectly until the datetime between @shiftastart and @shiftaend is added. I have queried the values for both variable and they what I need however when I add the statement I get the following error Must declare the scalar variable "@SHIFTASTART".
here is the complete query
DECLARE @shiftastart DATETIME2(7) DECLARE @shiftaend DATETIME2(7) SET @shiftastart = (select DATEADD(day,-1,shift_a_start) from v_dates where CAST(sysdatetime() as date)= CAST(shift_a_start as date)) SET @shiftaend = (select (DATEADD(day,-1,shift_a_end)) from v_dates where CAST(sysdatetime() as date)= CAST(shift_a_end as date))
declare @sql nvarchar(max); declare @tags varchar(max);
-- populate the list of tags dynamically in one statement select @tags = isnull(@tags + ', ', '') + '''' +RTRIM(tag_name) + '''' from simaTags where [aggregate] = 'max'; -- feed the list of tags to the final sql statement
select @sql = 'select max(datetime), TagName, max(value)as value from v_history where TagName in (' + @tags + ') and datetime between @shiftastart and @shiftaend group by tagname';exec sp_executesql @sql
Apr 27 '10 at 03:58 PM