Odd syntax issue - didn't happen in SQL 2000 - error in SQL 2005

Odd syntax issue - didn't happen in SQL 2000 - error in SQL 2005

Post by U3RldmUgW » Thu, 23 Aug 2007 05:46:03


I had this line in a very, very large stored procedure

If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0) Update #PY_Tbl Set After1987=1

Restored:

and it worked in SQL 2000 for years - now in SQL 2005 I got this error:

Incorrect Syntax near (-2147217900)

Changing the label RESTORED: to XYZ: make the error change to: ...near "X"

So I changed the code to this and it works now:

If Exists(Select * From #PM_Tbl Where ((AY=1986 and A>>=7) or A>>1987) and
PenHrs>>0)
Begin
Update #PY_Tbl Set After1987=1
End

Restored:

Any ideas why?
 
 
 

Odd syntax issue - didn't happen in SQL 2000 - error in SQL 2005

Post by Russell Fi » Thu, 23 Aug 2007 06:02:18

Steve,

SQL Server syntax is in the process of changing and becoming more ANSI.
That means that occasionally an assumption that 2000 was willing to make,
2005 is unwilling to make. The new approved syntax for ensuring that a
command is terminated is to close with ;

If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0) Update #PY_Tbl Set After1987=1;

Restored:

FWIW,
RLF

 
 
 

Odd syntax issue - didn't happen in SQL 2000 - error in SQL 2005

Post by JXSter » Fri, 24 Aug 2007 21:43:46

On Tue, 21 Aug 2007 13:46:03 -0700, Steve Z




What is the *next* statement?



J.