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

avatar image

Clint
100 6 6 10

(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

avatar image

John Franklin
414 1 3 7

(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

avatar image

Bhaskar
333 17 19 24

(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.

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:

x1069
x56
x29
x20
x6

asked: Apr 27, 2010 at 03:58 PM

Seen: 10460 times

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

Copyright 2016 Redgate Software. Privacy Policy