• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1349
  • Last Modified:

Error 170: Incorrect syntax near '=' on SQL Server 2000

hi SQL folks

i got stuck on the following error when modifying a stored procedure on SQL Server 2000, the Stored Procedure Properties window keeps showing the stop-sign message after pressing the Check Syntax button.

Error 170: Incorrect syntax near '='
this happened after adding the IF EXISTS () clause at line 11, before that there was no any error message.

the SQL server 2000 is running on Windows NT 4 server. yes, it is a NT 4 box. :-)

can you please help me determine where the problem is?

2 Solutions
Change the "if exists" part:

select @right = tag_right from tags where tag_key = @tag_id
if (@right is not null)
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if exists ( select @var = field ...

is not allowed in Sql Server.

I suggest you modify like this:

select @right = tag_right from  ....
if @right is not null
bbaoIT ConsultantAuthor Commented:
thank you all for the prompt replies. your comments did help!

as a C programmer who used to assign variable followed by comparison, sometimes it is really easy to have the same thinking when writing in other languages. :-)

the issue is that an assignment ("select @right = tag_right") was incorrectly given to a comparison ("if exists"). your comments did let me realise the reason.

actually, IF EXISTS () is allowed in SQL Server, but it requires a comparison to be followed. after correcting the code like below, it works now.

if exists (select tag_key from tags where tag_key = @tag_id)

thanks again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now