SQLTeam.com | Weblogs | Forums

MS SQL 2014 vs MS SQL 2005


#1

Hello,

I have a Stored Procedure that is running well in SQL 2014, but when I applied the same exact Stored Procedure (tried creating it), it shows the following error messages in SQL 2005:

Msg 156, Level 15, State 1, Procedure ABCDE, Line 11
Incorrect syntax near the keyword 'CONVERT'.
Msg 137, Level 15, State 2, Procedure ABCDE, Line 16
Must declare the scalar variable "@vReportStart".
Msg 137, Level 15, State 2, Procedure ABCDE, Line 125
Must declare the scalar variable "@startDate".
Msg 137, Level 15, State 2, Procedure ABCDE, Line 143
Must declare the scalar variable "@startDate".
Msg 102, Level 15, State 1, Procedure ABCDE, Line 155
Incorrect syntax near 'vlist'.
Msg 137, Level 15, State 2, Procedure ABCDE, Line 178
Must declare the scalar variable "@startDate".
Msg 137, Level 15, State 2, Procedure ABCDE, Line 219
Must declare the scalar variable "@startDate".
Msg 137, Level 15, State 2, Procedure ABCDE, Line 229
Must declare the scalar variable "@startDate".

Could someone guide me on how to declare the variables appropriate for 2005 as I thought they're already declared??

Here's the Stored Procedure (relevant portion at the beginning)...

CREATE PROCEDURE ABCDE
** (**
** @vStartDate date,**
** @vStopDate date**
** )**

AS
** BEGIN**

** DECLARE**
** @vReportStart date = CONVERT( DATE, isnull(@vStartDate, DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) - convert(int, (DAY(GETDATE() - 1) ))), 106), **
** @vReportEnd date = CONVERT(DATE, isnull(DATEADD(D, 1, @vStopDate), DATEADD(dd, DATEDIFF(dd, 0, getdate()), 1)), 106) **


** DECLARE**
** @startDate date = @vReportStart, **
** @endDate date = @vReportEnd**


#2

SQL Server 2005 doesn't support inline declaration and initialization. That feature was introduced in SQL Server 2008. You have to declare the variable then set it in a separate statement in 2005.