查询是否有这样一条记录，并且保存到一个变量！！急用
CREATE PROCEDURE CARRULE
@cCARID CHAR(4),
@cSTAUS CHAR(4),
@cRULE CHAR(4),
@rCnt CHAR(10)
AS
BEGIN
SELECT count(1) INTO rCnt FROM CAR_RULE WHERE CARID = @cCARID
IF(rCnt > 0) THEN//这里好像有问题
UPDATE CAR_RULE
SET STATUS = @cSTAUS, RULEINFO = @cRULE
WHERE CARID = @cCARID
ELSE
INSERT INTO CAR_RULE (CARID, STATUS, RULEINFO)
VALUES(@cCARID, @cSTAUS, @cRULE)
END IF
END
功能是这样的：如果数据库有这台车就更新数据，如果没有就插入一条新记录
[解决办法]
把这个SELECT count(1) INTO rCnt FROM CAR_RULE WHERE CARID = @cCARID
改成这个:
SELECT rCnt =count(1) INTO rCnt FROM CAR_RULE WHERE CARID = @cCARID
试一下
[解决办法]
if exists(select 1 FROM CAR_RULE WHERE CARID = @cCARID )
update
else
inert
判断语句格式:
if ...
else
...
[解决办法]
CREATE PROCEDURE CARRULE
@cCARID CHAR(4),
@cSTAUS CHAR(4),
@cRULE CHAR(4),
@rCnt CHAR(10)
AS
BEGIN
Declare @Count Int
SELECT @Count = count(1) FROM CAR_RULE WHERE CARID = @cCARID
IF(@Count > 0)
UPDATE CAR_RULE
SET STATUS = @cSTAUS, RULEINFO = @cRULE
WHERE CARID = @cCARID
ELSE
INSERT INTO CAR_RULE (CARID, STATUS, RULEINFO)
VALUES(@cCARID, @cSTAUS, @cRULE)
END
[解决办法]
CREATE PROCEDURE CARRULE
@cCARID CHAR(4),
@cSTAUS CHAR(4),
@cRULE CHAR(4),
@rCnt CHAR(10)
AS
declare @i int
BEGIN
SELECT @i=count(1) INTO #rCnt FROM CAR_RULE WHERE CARID = @cCARID
IF(@i > 0)
Begin
UPDATE CAR_RULE
SET STATUS = @cSTAUS, RULEINFO = @cRULE
WHERE CARID = @cCARID
end
ELSE
begin
INSERT INTO CAR_RULE (CARID, STATUS, RULEINFO)
VALUES(@cCARID, @cSTAUS, @cRULE)
end
END
[解决办法]
IF後面沒有THEN，也沒有END IF，你那不是SQL的語法。
賦值給變量，這麼寫
Declare @Count Int
SELECT @Count = count(1) FROM CAR_RULE WHERE CARID = @cCARID
[解决办法]
CREATE PROCEDURE CARRULE
@cCARID CHAR(4),
@cSTAUS CHAR(4),
@cRULE CHAR(4),
@rCnt CHAR(10)
AS
BEGIN
if exists (
SELECT 1 FROM CAR_RULE WHERE CARID = @cCARID
)
UPDATE CAR_RULE
SET STATUS = @cSTAUS, RULEINFO = @cRULE
WHERE CARID = @cCARID
ELSE
INSERT INTO CAR_RULE (CARID, STATUS, RULEINFO)
VALUES(@cCARID, @cSTAUS, @cRULE)
END IF
END
[解决办法]
應該改成這樣
CREATE PROCEDURE CARRULE
@cCARID CHAR(4),
@cSTAUS CHAR(4),
@cRULE CHAR(4),
@rCnt CHAR(10)
AS
BEGIN
SELECT @rCnt = count(1) FROM CAR_RULE WHERE CARID = @cCARID
IF(@rCnt > 0)
UPDATE CAR_RULE
SET STATUS = @cSTAUS, RULEINFO = @cRULE
WHERE CARID = @cCARID
ELSE
INSERT INTO CAR_RULE (CARID, STATUS, RULEINFO)
VALUES(@cCARID, @cSTAUS, @cRULE)
END
[解决办法]
呵，我没有改过来就发了!
把这个SELECT count(1) INTO rCnt FROM CAR_RULE WHERE CARID = @cCARID
改成这个:
SELECT rCnt =count(1) FROM CAR_RULE WHERE CARID = @cCARID
试一下
[解决办法]
select …… into 是将结果放在一个新表中去。不是变量赋值
