如何解决消息156，级别15，状态1，过程AddPostCodes，第23行关键字'where'附近的语法不正确。 [英] how to solve Msg 156, Level 15, State 1, Procedure AddPostCodes, Line 23 Incorrect syntax near the keyword 'where'.

问题描述

HEllo团队，



我想制作如下存储程序：



创建程序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

插入PINCODEMASTER（CUSTOMERLOANDATAID，CUSTOMERADDRESS1，CUSTOMERADDRESS2，LANDMARK，CITY，STATE，

PINCODE，ERRORMESSAGE）

< br $>
值（@ CUSTOMERLOANDATAID，@ CUSTOMERADDRESS1，@ CUSTOMERADDRESS2，@ LANDMARK，@ CITY，@ STATE，

@ PINCODE，@ ERRORMESSAGE）其中ID = @POSTCODESID



它给出错误

消息156，级别15，状态1，过程AddPostCodes，第23行

关键字'where'附近的语法不正确。



请指导我出错的地方..

谢谢

Harshal

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

推荐答案

使用 INSERT 语句的事实表明数据库中尚不存在相应的行。所以在这种情况下， WHERE 语句是无关紧要的（如果该行不存在，则匹配其id没有意义）。



所以，然而：

- 该行不存在=>只需丢弃 WHERE 部分和@POSTCODESID参数。

- 该行已存在=>你需要一个 UPDATE 语句而不是 INSERT 一个。

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.

INSERT没有WHERE子句 - 它总是创建一个新记录。

你的意思是改为更新吗？

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

不，你不能在你的WHERE子句中INSERT语句



您需要更改INSERT语句

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 

这篇关于如何解决消息156，级别15，状态1，过程AddPostCodes，第23行关键字'where'附近的语法不正确。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！