数据库对接接口存储过程

USE [CS]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[CS]--执行插入需要用到的参数(@ERPID  int out,        --单据ID@ShopId int out,        --商品ID@KRec   int out,        --仓库ID@Quantity int out,      --购买数量@RealQuantity int out,  --开票数量@Price  int out,        --成本单价@SalePrice money out,   --销售价含税@ReachQty int out,      --执行数量--返回值参数@scmaxid int output,    --商城端最大订单号@qfmaxid int output     --千方端最大订单号)ascreate table #a --创建临时表#a储存查询的结果集(   ID   int IDENTITY (0,1),--自增列从0开始每次增加1   ERPid int,--单据ID   ssId int, --商品ID   Kc  int,  --仓库ID   Qy  int,  --购买数量   Ry  int,  --开票数量   Pe  int,  --成本单价   Se  money,--销售价含税   RQty int  --执行数量 ); --将查询结果集插入到临时表中INSERT INTO #a SELECT DYSmall_OrderItems.CSOrderId AS ERP单据ID, DYSmall_Orders.ShopId AS 商品ID,dbo.GoodsStocks.KRec AS 仓库ID,DYSmall_OrderItems.Quantity AS 购买数量,DYSmall_OrderItems.RealQuantity AS 开票数量, dbo.GoodsStocks.Price AS 成本单价,DYSmall_OrderItems.SalePrice AS 销售价含税,DYSmall_OrderSendGoodItems.Quantity AS 执行数量FROM  OPENQUERY(DASHANG, 'select * from dysb2b2b.DYSmall_OrderItems') as DYSmall_OrderItemsINNER JOIN OPENQUERY(DASHANG, 'select * from dysb2b2b.DYSmall_Orders') as DYSmall_OrdersON DYSmall_OrderItems.OrderId = DYSmall_Orders.Id INNER JOIN OPENQUERY(DASHANG, 'select * from dysb2b2b.DYSmall_OrderSendGoodItems') as DYSmall_OrderSendGoodItemsON DYSmall_OrderItems.OrderId = DYSmall_OrderSendGoodItems.OrderIdleft JOIN dbo.GoodsStocks ON dbo.GoodsStocks.PRec = DYSmall_Orders.ShopId--定义临时变量declare @i int,        --标记临时表的ID@B int,        --ERP订单ID@S int,        --商品ID@K int,        --仓库ID@Q int,        --购买数量@R int,        --开票数量@P int,        --成本单价@S2 money,     --销售价含税@R2 int        --执行数量--千方表中最大订单号select @qfmaxid = MAX(BillID) from OrderBill--创建临时变量declare@ii int     --循环更新变量set @ii=0   --从0开始--查询临时表a的ID总数赋值给i控制循环次数select @i = count(ID) from #a;--循环判断WHILE @ii<@i     BEGIN--商城端表中最大ERP订单号select @scmaxid = ERPid from #a where ID=@iiif(@scmaxid>@qfmaxid)begin--查询出临时表中的单据ID对变量赋值select @B = ERPid from #a where ERPid=@scmaxid;--查询出临时表中的商品ID对变量赋值select @S = ssId from #a where ERPid=@scmaxid;--查询出临时表中的仓库ID对变量赋值select @K = Kc from #a where ERPid=@scmaxid;--查询出临时表中的购买数量对变量赋值select @Q = Qy from #a where ERPid=@scmaxid;--查询出临时表中的开票数量对变量赋值select @R = Ry from #a where ERPid=@scmaxid;--查询出临时表中的成本单价对变量赋值select @P = Pe from #a where ERPid=@scmaxid;--查询出临时表中的销售单价含税对变量赋值select @S2 = Se from #a where ERPid=@scmaxid;--查询出临时表中的开票数量对变量赋值select @R2 = RQty from #a where ERPid=@scmaxid;--将变量的值传入参数中set @ERPID = @B;set @ShopId = @S;set @KRec = @K;set @Quantity = @Q;set @RealQuantity = @R;set @Price = @P;set @SalePrice = @S2;set @ReachQty = @R2; insert into [OrderBill](BillID,PRec,KRec,qty,AssQty,Price,TaxPrice,ReachQty)values(@ERPID,@ShopId,@KRec,@Quantity,@RealQuantity,@Price,@SalePrice,@ReachQty)end elseif(@scmaxid<@qfmaxid)breakSET @ii=@ii+1 --更新循环变量END--删除临时表 drop table #a