Must declare the scalar variable "@SHIFTASTART".

Question

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

Answer 1

0

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.

answered Apr 27 '10 at 04:23 PM

John Franklin
414 ● 1 ● 1 ● 3

that worked thank you so much ... i read the books and tried everything i could think of but your code was just enough different to help me undersand

Apr 27 '10 at 07:26 PM Clint

(comments are locked)

Answer 2

0

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'

answered Apr 28 '10 at 10:38 AM

Bhaskar
333 ● 15 ● 16 ● 20

(comments are locked)

Must declare the scalar variable "@SHIFTASTART".

Follow this question

Related Questions