x

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
more ▼

asked Apr 27, 2010 at 03:58 PM in Default

Clint gravatar image

Clint
100 6 6 7

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Apr 27, 2010 at 04:23 PM

John Franklin gravatar image

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, 2010 at 07:26 PM Clint
(comments are locked)
10|1200 characters needed characters left

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'

more ▼

answered Apr 28, 2010 at 10:38 AM

Bhaskar gravatar image

Bhaskar
333 16 17 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x51
x27
x17
x4

asked: Apr 27, 2010 at 03:58 PM

Seen: 9663 times

Last Updated: Apr 28, 2010 at 03:37 AM