存储过程如下:现在遇到的问题是如果查询 PIMA 表中的数据为空的时候 @dd_id 、@ItemID 、@ItemName 的值并不为空,而是被赋予了前面最后一个不为空的值,想了很久也不晓得是什么原因,谁能告诉我下原因吗?我只有这么多分了,请各位帮帮忙吧,谢谢!alter PROCEDURE [dbo].[CopyItem1]
AS
declare
@LB varchar(100) ,--类型
@PM varchar(30),--品名
@GG varchar(50),--规格
@COLO varchar(20),--颜色
@GSM int,--克重
@CD varchar(30),--产地
@DW varchar(30),--单位
@SL float,--数量
@zhxs float,--转化系数
@ItemID varchar(50),--物料号
@ItemName varchar(100),--物料名称
@L_ZL float,--重量
@L_SL float,--数量
@i int,--循环变量
@dd_id int,
@min_id int,--仓库临时表L_spacewar最小id
@max_id int, --仓库临时表L_spacewar最大ID
@sql varchar(1000)
BEGIN
BEGIN TRANSACTION
select @min_id = min(id), @max_id = max(id) from L_spacewar; -- 获取L_spacewar总行数
set @i=@min_id;
BEGIN
while @i<@max_id+1
begin
--从L_spacewar取数据
select @LB=LB, @PM=PM, @GG=GG, @COLO=COLO, @GSM=GSM, @CD=CD, @DW=DW, @SL=SL from L_spacewar where id = @i;
print(@lb+@pm+@gg+@colo+cast(@gsm as nchar(20))+@cd)
select @dd_id=isnull(id,0), @ItemID =isnull(ItemNum,''), @ItemName = isnull(ItemName,''), @zhxs=zhxs
from PIMA where ((ltrim(rtrim(Type))=ltrim(rtrim(@LB))) and (ltrim(rtrim(Name))=ltrim(rtrim(@PM))) and (ltrim(rtrim(Norm))=ltrim(rtrim(@GG))) and (ltrim(rtrim(Color))=ltrim(rtrim(@COLO))) and (Gram=@GSM) and (ltrim(rtrim(Region))=ltrim(rtrim(@CD))));set @sql ='select @dd_id=id, @ItemID =ItemNum, @ItemName = ItemName, @zhxs=zhxs from PIMA where ((ltrim(rtrim(Type))='+@LB+') and (ltrim(rtrim(Name))='+@PM+') and (ltrim(rtrim(Norm))='+@GG+') and (ltrim(rtrim(Color))='+@COLO+') and (Gram='+cast(@GSM as nchar(5))+') and (ltrim(rtrim(Region))='+@CD+'))';
print(@sql) print(cast(@dd_id as nchar(5))+@itemid+@itemname)
--计算平板纸重量
if @zhxs >0
begin
SET @L_ZL = (@zhxs) * (@SL);
SET @L_SL = @SL;
end
else
BEGIN
SET @L_ZL = @SL;
SET @L_SL=0;
END
insert into E_Spacewar (ItemNum,ItemName,SpaceCode,Amount,UnitMeasure,Type,SpaceName,DepotName,Quantity,zhxs,ShopNumber,IsBack,Norm)
values(@ItemID, @ItemName,'C_RSC', @L_ZL, @DW,0,'实存区','盘龙山纸库', @L_SL, @zhxs,'',0, @GG);
set @dd_id = '';
set @ItemID = '';
set @i=@i+1 ;
END
END
COMMIT TRANSACTION
END
AS
declare
@LB varchar(100) ,--类型
@PM varchar(30),--品名
@GG varchar(50),--规格
@COLO varchar(20),--颜色
@GSM int,--克重
@CD varchar(30),--产地
@DW varchar(30),--单位
@SL float,--数量
@zhxs float,--转化系数
@ItemID varchar(50),--物料号
@ItemName varchar(100),--物料名称
@L_ZL float,--重量
@L_SL float,--数量
@i int,--循环变量
@dd_id int,
@min_id int,--仓库临时表L_spacewar最小id
@max_id int, --仓库临时表L_spacewar最大ID
@sql varchar(1000)
BEGIN
BEGIN TRANSACTION
select @min_id = min(id), @max_id = max(id) from L_spacewar; -- 获取L_spacewar总行数
set @i=@min_id;
BEGIN
while @i<@max_id+1
begin
--从L_spacewar取数据
select @LB=LB, @PM=PM, @GG=GG, @COLO=COLO, @GSM=GSM, @CD=CD, @DW=DW, @SL=SL from L_spacewar where id = @i;
print(@lb+@pm+@gg+@colo+cast(@gsm as nchar(20))+@cd)
select @dd_id=isnull(id,0), @ItemID =isnull(ItemNum,''), @ItemName = isnull(ItemName,''), @zhxs=zhxs
from PIMA where ((ltrim(rtrim(Type))=ltrim(rtrim(@LB))) and (ltrim(rtrim(Name))=ltrim(rtrim(@PM))) and (ltrim(rtrim(Norm))=ltrim(rtrim(@GG))) and (ltrim(rtrim(Color))=ltrim(rtrim(@COLO))) and (Gram=@GSM) and (ltrim(rtrim(Region))=ltrim(rtrim(@CD))));set @sql ='select @dd_id=id, @ItemID =ItemNum, @ItemName = ItemName, @zhxs=zhxs from PIMA where ((ltrim(rtrim(Type))='+@LB+') and (ltrim(rtrim(Name))='+@PM+') and (ltrim(rtrim(Norm))='+@GG+') and (ltrim(rtrim(Color))='+@COLO+') and (Gram='+cast(@GSM as nchar(5))+') and (ltrim(rtrim(Region))='+@CD+'))';
print(@sql) print(cast(@dd_id as nchar(5))+@itemid+@itemname)
--计算平板纸重量
if @zhxs >0
begin
SET @L_ZL = (@zhxs) * (@SL);
SET @L_SL = @SL;
end
else
BEGIN
SET @L_ZL = @SL;
SET @L_SL=0;
END
insert into E_Spacewar (ItemNum,ItemName,SpaceCode,Amount,UnitMeasure,Type,SpaceName,DepotName,Quantity,zhxs,ShopNumber,IsBack,Norm)
values(@ItemID, @ItemName,'C_RSC', @L_ZL, @DW,0,'实存区','盘龙山纸库', @L_SL, @zhxs,'',0, @GG);
set @dd_id = '';
set @ItemID = '';
set @i=@i+1 ;
END
END
COMMIT TRANSACTION
END
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货