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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Incorrect syntax near the keyword 'ORDER'

Author  Topic 

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2010-07-08 : 17:43:45
Hi,
I got an error but can't figure it out.
Incorrect syntax near the keyword 'ORDER'

Thanks for help.
DECLARE @TEMP VARCHAR(50)
SET @TEMP = 'A'

IF @TEMP=''
BEGIN
(SELECT TOP 10 EMP_ID AS EmployeeID, LST_NM + ', ' + FRST_NM AS FullName FROM EMPLOYEE
where EMP_ID NOT IN (SELECT TOP 10 EMP_ID FROM EMPLOYEE)
ORDER BY EMP_ID)
END
ELSE
BEGIN
(SELECT TOP 10 EMP_ID AS EmployeeID, LST_NM + ', ' + FRST_NM AS FullName FROM EMPLOYEE WHERE (UPPER(EMP_ID) LIKE UPPER(@TEMP)) OR ((LST_NM + ', ' + FRST_NM) LIKE UPPER(@TEMP+'%'))
AND EMP_ID NOT IN (SELECT TOP 10 EMP_ID FROM EMPLOYEE WHERE (EMP_ID LIKE @TEMP+'%') OR ((LST_NM + ', ' + FRST_NM) LIKE UPPER(@TEMP+'%')))
ORDER BY EMP_ID)
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-08 : 17:46:27
Remove the parenthesis:

DECLARE @TEMP VARCHAR(50)
SET @TEMP = 'A'

IF @TEMP=''
BEGIN
SELECT TOP 10 EMP_ID AS EmployeeID, LST_NM + ', ' + FRST_NM AS FullName FROM EMPLOYEE
where EMP_ID NOT IN (SELECT TOP 10 EMP_ID FROM EMPLOYEE)
ORDER BY EMP_ID
END
ELSE
BEGIN
SELECT TOP 10 EMP_ID AS EmployeeID, LST_NM + ', ' + FRST_NM AS FullName FROM EMPLOYEE WHERE (UPPER(EMP_ID) LIKE UPPER(@TEMP)) OR ((LST_NM + ', ' + FRST_NM) LIKE UPPER(@TEMP+'%'))
AND EMP_ID NOT IN (SELECT TOP 10 EMP_ID FROM EMPLOYEE WHERE (EMP_ID LIKE @TEMP+'%') OR ((LST_NM + ', ' + FRST_NM) LIKE UPPER(@TEMP+'%')))
ORDER BY EMP_ID
END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2010-07-08 : 17:50:26
Thanks. It works out.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-08 : 17:55:15
You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -