Must declare the scalar variable "@SHIFTASTART".

Question

question

Clint asked Apr 27, '10

Must declare the scalar variable "@SHIFTASTART".

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

t-sql dynamic variable execute scalar

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-04-27T16:23:27Z

John Franklin answered Apr 27, '10

Your dynamic SQL statement does not know about your two date variables. You need to pass those values into the sp_executesql statement similar to the following example...


DECLARE @dtStart  datetime
DECLARE @dtEnd    datetime

SET @dtStart  = '4/25/2010'
SET @dtEnd    = '4/26/2010'

DECLARE @sSQL nvarchar(4000)

SET @sSQL = ' SELECT  *
              FROM    PROCESS_LOG
              WHERE   MSG_TIME  BETWEEN @dtStart
                                  AND   @dtEnd  '

EXEC sp_executesql  @sSQL,
                    N'@dtStart datetime, @dtEnd datetime',
                    @dtStart, @dtEnd

You can review the syntax of sp_executesql in BOL.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-04-28T10:38:05Z

Bhaskar answered Apr 28, '10

or just change @sql statement to

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'

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question