SQL View error @ declare statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL View error @ declare statement

Author

Topic

Julie19
Starting Member

32 Posts

Posted - 2012-06-26 : 09:03:54

declare @dt date, @dt1 date, @dt2 date, @dt3 date, @dt4 date, @dt5
date, @dt6 ,@dt7,@dt8,@dt9,@dt10,@dt11,@dt12 date
declare @sql varchar(MAX)
set @dt = getdate()
set @dt1 = dateadd(day, -1, getdate())
set @dt2 = dateadd(day, -2, getdate())
set @dt3 = dateadd(day, -3, getdate())
set @dt4 = dateadd(day, -4, getdate())
set @dt5 = dateadd(day, -5, getdate())
set @dt6 = dateadd(day, -6, getdate())
set @dt7 = dateadd(day, -7, getdate())
set @dt8 = dateadd(day, -8, getdate())
set @dt9 = dateadd(day, -9, getdate())
set @dt10 = dateadd(day, -10, getdate())
set @dt11 = dateadd(day, -11, getdate())
set @dt12 = dateadd(day, - 12, getdate())

I am getting the error
Incorrect syntax near ","
Must declare the scalar variable "@dt"
Must declare the scalar variable "@dt1"
Must declare the scalar variable "@dt2"
Must declare the scalar variable "@dt3"
....
....
Must declare the scalar variable "@dt12"

Please help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-26 : 09:13:02

declare @dt date, @dt1 date, @dt2 date, @dt3 date, @dt4 date, @dt5
date, @dt6 date ,@dt7 date ,@dt8 date ,@dt9 date ,@dt10 date ,@dt11 date ,@dt12 date

No, you're never too old to Yak'n'Roll if you're too young to die.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-26 : 09:13:53

Look at the syntax please:

declare @dt date, @dt1 date, @dt2 date, @dt3 date, @dt4 date, @dt5
date, @dt6 ,@dt7,@dt8,@dt9,@dt10,@dt11,@dt12 date

You are missing the data type.

try this:
declare @dt date, @dt1 date, @dt2 date, @dt3 date, @dt4 date, @dt5
date, @dt6 date,@dt7 date,@dt8 date,@dt9 date,@dt10 date,@dt11 date,@dt12 date

luck,

--------------------------
Get rich or die trying
--------------------------

Julie19
Starting Member

32 Posts

Posted - 2012-06-26 : 11:23:32

Can somebody tell me what is wrong in this code.I am getting a Execution error

declare @dt date, @dt1 date, @dt2 date, @dt3 date, @dt4 date, @dt5
date, @dt6 date,@dt7 date,@dt8 date,@dt9 date,@dt10 date,@dt11 date,@dt12 date,@dt13 date,@dt14 date

declare @sql varchar(MAX)
set @dt = getdate()
set @dt1 = dateadd(day, -1, getdate())
set @dt2 = dateadd(day, -2, getdate())
set @dt3 = dateadd(day, -3, getdate())
set @dt4 = dateadd(day, -4, getdate())
set @dt5 = dateadd(day, -5, getdate())
set @dt6 = dateadd(day, -6, getdate())
set @dt7 = dateadd(day, -7, getdate())
set @dt8 = dateadd(day, -8, getdate())
set @dt9 = dateadd(day, -9, getdate())
set @dt10 = dateadd(day, -10, getdate())
set @dt11 = dateadd(day, -11, getdate())
set @dt12 = dateadd(day, - 12, getdate())
set @dt13 = dateadd(day, - 13, getdate())
set @dt14 = dateadd(day, - 14, getdate())

set @SQL = 'SELECT a.RptFunction, a.Center, a.[ATTUID], a.Agent
,SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt12,120) + '''' + ' THEN a.daily1day ELSE 0)END AS [ ' + convert(varchar(25), @dt14, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt12, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0) END AS [ ' + convert(varchar(25), @dt14, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt11, 120) + '''' + ' THEN a.daily1day ELSE 0 )END AS [ ' + convert(varchar(25), @dt13, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt11, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0) END AS [ ' + convert(varchar(25), @dt13, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt10, 120) + '''' + ' THEN a.daily1day ELSE 0) END AS [ ' + convert(varchar(25), @dt12, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt10, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0) END AS [ ' + convert(varchar(25), @dt12, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt9, 120) + '''' + ' THEN a.daily1day ELSE 0) END AS [ ' + convert(varchar(25), @dt11, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt9, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0) END AS [ ' + convert(varchar(25), @dt11, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt8, 120) + '''' + ' THEN a.daily1day ELSE 0) END AS [ ' + convert(varchar(25), @dt10, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt8, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0) END AS [ ' + convert(varchar(25), @dt10, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt7, 120) + '''' + ' THEN a.daily1day ELSE 0 )END AS [ ' + convert(varchar(25), @dt9, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + convert(varchar(25), @dt7, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 )END AS [ ' + convert(varchar(25), @dt9, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt6, 120) + '''' + ' THEN a.daily1day ELSE 0) END AS [ ' + convert(varchar(25), @dt8, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt6, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0) END AS [ ' + convert(varchar(25), @dt8, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt5, 120) + '''' + ' THEN a.daily1day ELSE 0 )END AS [ ' + convert(varchar(25), @dt7, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt5, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0) END AS [ ' + convert(varchar(25), @dt7, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt4, 120) + '''' + ' THEN a.daily1day ELSE 0) END AS [ ' + convert(varchar(25), @dt6, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + convert(varchar(25), @dt4, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0) END AS [ ' + convert(varchar(25), @dt6, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + convert(varchar(25), @dt3, 120) + '''' + ' THEN a.daily1day ELSE 0 )END AS [ ' + convert(varchar(25), @dt5, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt3, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 )END AS [ ' + convert(varchar(25), @dt5, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + convert(varchar(25), @dt2, 120) + '''' + ' THEN a.daily1day ELSE 0) END AS [ ' + convert(varchar(25), @dt4, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt2, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 )END AS [ ' + convert(varchar(25), @dt4, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + convert(varchar(25), @dt1, 120) + '''' + ' THEN a.daily1day ELSE 0) END AS [ ' + convert(varchar(25), @dt3, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt1, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0 )END AS [ ' + convert(varchar(25), @dt3, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' + convert(varchar(25), @dt, 120) + '''' + ' THEN a.daily1day ELSE 0 )END AS [ ' + convert(varchar(25), @dt2, 120) + ']'
+ ',SUM(CASE WHEN convert(varchar(10), ReportDate) = ' + '''' +convert(varchar(25), @dt, 120) + '''' + ' THEN a.[1DayDailyAttain] ELSE 0) END AS [ ' + convert(varchar(25), @dt2, 120) + ']'

+ ' FROM [dbo].[q_Agent] a where int_ext = ' + '''' + 'i' + ''''
+ ' GROUP BY a.RptFunction, a.Center, a.[ATTUID], a.Agent'
print @sql
exec (@sql)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-06-26 : 11:37:51

all your: sum(case when...else 0) end as []
have the end parentheses in the wrong place. the final parenthesis Needs to come after the "end".

Be One with the Optimizer
TG

Julie19
Starting Member

32 Posts

Posted - 2012-06-26 : 12:38:09

Thanks a lot

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-06-26 : 12:48:26

You're welcome

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources