SQL2008下建立如下存储过程,总是有提示 对象名无效:
@StockBill_ID 是参数 Select b.StockBillDetail_MaterialID AS S_MaterialID,
a.StockBill_WarehouseID AS S_WarehouseID,
SUM(b.StockBillDetail_Qty) AS S_Qty
into #tmpStockOpening
from w_StockBill a,w_StockBillDetail b
where a.StockBill_ID=b.StockBillDetail_ParentID AND b.StockBillDetail_Status =0
and a.StockBill_ID= @StockBill_ID and a.StockBill_Status = 0
group by b.StockBillDetail_MaterialID,a.StockBill_WarehouseID;
--Inventory库存处理
--更新已经存在的
update c set c.Inventory_Qty = c.Inventory_Qty+S_Qty
from w_Inventory c,#tmpStockOpening
where c.Inventory_MaterialID = S_MaterialID
and c.Inventory_WarehouseID = S_WarehouseID ;
--插入不存在的
insert w_Inventory(Inventory_MaterialID,Inventory_FromID,
Inventory_WarehouseID,Inventory_Qty)
select S_MaterialID,S_FromID,S_WarehouseID,S_Qty
from #tmpStockOpening
where not exists(
select * from w_Inventory
where Inventory_MaterialID = S_MaterialID
and Inventory_WarehouseID = S_WarehouseID
); 这样依次执行的 是否运行结果会有问题??网上查到 :
DECLARE @Sql1 varchar(8000),@Sql2 varchar(8000),@Sql3 varchar(8000)SET @Sql1 = '......' --上面对应第一个SQL执行语句
SET @Sql2 = '......'
SET @Sql3 = '......'
EXEC(@Sql1+@Sql2+@Sql3);这是可行好方法吗?
@StockBill_ID 是参数 Select b.StockBillDetail_MaterialID AS S_MaterialID,
a.StockBill_WarehouseID AS S_WarehouseID,
SUM(b.StockBillDetail_Qty) AS S_Qty
into #tmpStockOpening
from w_StockBill a,w_StockBillDetail b
where a.StockBill_ID=b.StockBillDetail_ParentID AND b.StockBillDetail_Status =0
and a.StockBill_ID= @StockBill_ID and a.StockBill_Status = 0
group by b.StockBillDetail_MaterialID,a.StockBill_WarehouseID;
--Inventory库存处理
--更新已经存在的
update c set c.Inventory_Qty = c.Inventory_Qty+S_Qty
from w_Inventory c,#tmpStockOpening
where c.Inventory_MaterialID = S_MaterialID
and c.Inventory_WarehouseID = S_WarehouseID ;
--插入不存在的
insert w_Inventory(Inventory_MaterialID,Inventory_FromID,
Inventory_WarehouseID,Inventory_Qty)
select S_MaterialID,S_FromID,S_WarehouseID,S_Qty
from #tmpStockOpening
where not exists(
select * from w_Inventory
where Inventory_MaterialID = S_MaterialID
and Inventory_WarehouseID = S_WarehouseID
); 这样依次执行的 是否运行结果会有问题??网上查到 :
DECLARE @Sql1 varchar(8000),@Sql2 varchar(8000),@Sql3 varchar(8000)SET @Sql1 = '......' --上面对应第一个SQL执行语句
SET @Sql2 = '......'
SET @Sql3 = '......'
EXEC(@Sql1+@Sql2+@Sql3);这是可行好方法吗?
假如你的运行代码的会话,叫做会话1,那么创建的临时表只能存在会话1里就算exec的命令也在会话1中运行,但实际exec内部会产生会话2去运行exec中包含的语句。也就是说会话2中不会存在会话1的临时表,替换方法可以采用全局临时表##表名,局部临时表是#表名,或者将局部临时表#表替换成物理表即可