how to solve Msg 156, Level 15, State 1, Procedure AddPostCodes, Line 23 Incorrect syntax near the keyword 'where'.

Question

1.00/5 (1 vote)

See more:

HEllo Team,

I want to make the Stored Procedure as follow:

CREATE PROCEDURE AddPostCodes
@POSTCODESID INT ,
@CUSTOMERLOANDATAID int,
@CUSTOMERADDRESS1 varchar(100),
@CUSTOMERADDRESS2 varchar(100),
@LANDMARK varchar(50),
@CITY varchar(25),
@STATE varchar(25),
@PINCODE varchar(7),
@ERRORMESSAGE varchar(max)

AS
BEGIN
insert into PINCODEMASTER(CUSTOMERLOANDATAID,CUSTOMERADDRESS1,CUSTOMERADDRESS2,LANDMARK,CITY,STATE,
PINCODE, ERRORMESSAGE)

values(@CUSTOMERLOANDATAID,@CUSTOMERADDRESS1,@CUSTOMERADDRESS2,@LANDMARK,@CITY,@STATE,
@PINCODE,@ERRORMESSAGE) where ID = @POSTCODESID

it gives the error
Msg 156, Level 15, State 1, Procedure AddPostCodes, Line 23
Incorrect syntax near the keyword 'where'.

please guide me where i am getting wrong..
Thanks
Harshal

Posted 23-Feb-14 21:44pm

R Harshal

Updated 4-Feb-22 7:55am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2014-02-23T21:56:00

The fact that you use an INSERT statement suggests that the corresponding line does not exist yet in your database. So in this context the WHERE statement is irrelevant (if the line does not exist, there is no point in matching its id).

So, whereas:
- the line does not exist => just throw away the WHERE part and the @POSTCODESID parameter.
- the line already exist => you need an UPDATE statement instead of an INSERT one.

OriginalGriff · Answer 2 · 2014-02-23T22:01:00

Solution 3

INSERT does not have a WHERE clause - it always creates a new record.
Did you mean to UPDATE instead?

Posted 23-Feb-14 22:01pm

OriginalGriff

Ramug10 · Answer 3 · 2014-02-23T21:59:00

No, you can't have WHERE clause in your INSERT statement

You need to change your INSERT statement

CREATE PROCEDURE AddPostCodes
 @POSTCODESID INT ,
 @CUSTOMERLOANDATAID int,
 @CUSTOMERADDRESS1 varchar(100),
 @CUSTOMERADDRESS2 varchar(100),
 @LANDMARK varchar(50),
 @CITY varchar(25),
 @STATE varchar(25),
 @PINCODE varchar(7),
 @ERRORMESSAGE varchar(max)
  
 AS
 BEGIN
 Declare @ID INT = 0
 Select @ID = ID From PINCODEMASTER where ID = @POSTCODESID
 IF(@ID <> 0 AND @ID = @POSTCODESID)
 BEGIN
 insert into PINCODEMASTER(CUSTOMERLOANDATAID,CUSTOMERADDRESS1,CUSTOMERADDRESS2,LANDMARK,CITY,STATE,
 PINCODE, ERRORMESSAGE)
  
 values(@CUSTOMERLOANDATAID,@CUSTOMERADDRESS1,@CUSTOMERADDRESS2,@LANDMARK,@CITY,@STATE,
 @PINCODE,@ERRORMESSAGE)
 END
 END

how to solve Msg 156, Level 15, State 1, Procedure AddPostCodes, Line 23 Incorrect syntax near the keyword 'where'.

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0