存储过程如何处理临时变量？我的例子怎样改?
create PROCEDURE ment (
@pvolumeNUMERIC(10,0),
@pweightNUMERIC(10,0),
@ptripIDvarchar(5),
@vVOL NUMERIC(10,0),
@vTRUCK_VOL NUMERIC(10,0)
) AS
BEGIN
SELECT SUM(volume) into @vVOL
FROM t_Shipment
WHERE tripID=@ptripID
SELECT vol_capacity into @vTRUCK_VOL
FROM t_Trip p, t_Truck k
WHERE p.truckID=k.truckID and p.tripID=@ptripID
IF( (@vVOL+@pvolume)> @vTRUCK_VOL ) begin
print 'Volume is too large for this trip ';
RETURN;
end
ELSE begin
print 'OK ' ;
end
INSERT INTO t_Shipment VALUES (@pvolume, @pweight, @ptripID);
COMMIT;
END
======================================================================
消息 102，级别 15，状态 1，过程 ment，第 12 行
'@vVOL ' 附近有语法错误。
消息 102，级别 15，状态 1，过程 ment，第 16 行
'@vTRUCK_VOL ' 附近有语法错误。
======================================================================
功能描述：
如果一辆车的累计装载量vVoL + 本次装载量pvolume > 指定累计装载量vTRUCK_VOL 则执行插入语句
表已建好，如下（检查无问题）
CREATE TABLE t_Trip
(tripID numeric(5,0) IDENTITY(10001,1) primary key,
tripdatetimedatetime,
truckIDvarchar(4)
);
CREATE TABLE t_Truck
(truckIDvarchar(4) primary key,
vol_capacitynumeric(10,0),
weight_capacitynumeric(10,0)
);
CREATE TABLE t_Shipment
(shipmentIDnumeric(5,0) IDENTITY(10001,1) primary key,
volumenumeric(10,0),
weightnumeric(10,0),
tripIDvarchar(4)
);
[解决办法]
create PROCEDURE ment (
@pvolume NUMERIC(10,0),
@pweight NUMERIC(10,0),
@ptripID numeric(5,0),
@vVOL NUMERIC(10,0),
@vTRUCK_VOL NUMERIC(10,0)
) AS
BEGIN
SELECT @vVOL=SUM(volume)
FROM t_Shipment WHERE tripID=@ptripID
SELECT @vTRUCK_VOL=vol_capacity
FROM t_Trip p, t_Truck k
WHERE p.truckID=k.truckID and p.tripID=@ptripID
IF( (@vVOL+@pvolume)> @vTRUCK_VOL )
begin
print 'Volume is too large for this trip '
RETURN
end
ELSE
begin
print 'OK '
end
INSERT INTO t_Shipment VALUES (@pvolume, @pweight, @ptripID)
END
[解决办法]
把SELECT SUM(volume) into @vVOL
FROM t_Shipment
WHERE tripID=@ptripID
改为
SELECT @vVOL=SUM(volume)
FROM t_Shipment
WHERE tripID=@ptripID
[解决办法]
SELECT SUM(volume) into @vVOL =====> SELECT @vVOL=SUM(volume)
SELECT vol_capacity into @vTRUCK_VOL ====> SELECT @vTRUCK_VOL=vol_capacity
