2.sql

将excel格式的文件销售订单导入数据库中

drop Procedure [dbo].[sp_xiaoshou]       
        
create Procedure [dbo].[sp_xiaoshou]          
(    
  @user varchar(20),   
 @ccuscode varchar(20),     
 @cinvcode varchar(20),    
 @iquantity numeric(18,4), 
 @iprice numeric(18,4),  
 @imoney numeric(18,4)     
)    
as             
set nocount on            
BEGIN TRANSACTION      
declare @ccode varchar(20)     
declare @p0 int    
declare @p1 int          
declare @p2 int          
declare @cdeptcode varchar(20)  
declare @ccusname varchar(50) 
declare @cinvname varchar(50) 
begin 
  set @ccuscode=ltrim(rtrim(@ccuscode))
  set @user=ltrim(rtrim(@user))
  set @cinvcode=ltrim(rtrim(@cinvcode))
 set @p0=1
exec sp_GetId N'', N'888', N'Somain', @p0, @P1 output, @P2 output     
 select @ccusname=ccusname from customer where ccuscode=@ccuscode
 select @cinvname=cinvname from inventory where cinvcode=@cinvcode
select @ccode=right('0000000000'+ltrim(rtrim(convert(varchar(20),convert(int,cnumber)+1))),10) from VoucherHistory  Where  CardNumber='17' and cContent is NULL          
update VoucherHistory set cNumber=convert(varchar(20),convert(int,cnumber)+1) Where  CardNumber='17' and cContent is NULL          
end    
begin           
insert into so_somain ([cSTCode],[dDate],[cSOCode],[cCusCode],[cDepCode],[cPersonCode],[cSCCode],[cCusOAddress],[cPayCode],[cexch_name],[iExchRate],[iTaxRate],[iMoney],[cMemo],[iStatus],[cMaker],[cVerifier],[cCloser],[bDisFlag],[cDefine1],[cDefine2],[cDefine3],[cDefine4],[cDefine5],[cDefine6],[cDefine7],[cDefine8],[cDefine9],[cDefine10],[bReturnFlag],[cCusName],[bOrder],[ID],[iVTid],[cChanger],[cBusType],[cCreChpName],[cDefine11],[cDefine12],[cDefine13],[cDefine14],[cDefine15],[cDefine16],[coppcode],[cLocker],[dPreMoDateBT],[dPreDateBT],[cgatheringplan],[caddcode],[iverifystate],[ireturncount],[iswfcontrolled],[icreditstate])
values('01',getdate(),@ccode,@ccuscode,'201',Null,Null,Null,Null,'人民币',1,17,Null,Null,1,'demo','demo',Null,'0',Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,'0',@ccusname,'0',@P1,95,Null,'普通销售',Null,Null,Null,Null,Null,Null,Null,Null,Null,getdate(),getdate(),Null,Null,0,Null,Null,Null)
insert into so_sodetails ([cSOCode],[cInvCode],[dPreDate],[iQuantity],[iNum],[iQuotedPrice],[iUnitPrice],[iTaxUnitPrice],[iMoney],[iTax],[iSum],[iDisCount],[iNatUnitPrice],[iNatMoney],[iNatTax],[iNatSum],[iNatDisCount],[iFHNum],[iFHQuantity],[iFHMoney],[iKPQuantity],[iKPNum],[iKPMoney],[cMemo],[cFree1],[cFree2],[bFH],[iSOsID],[KL],[KL2],[cInvName],[iTaxRate],[cDefine22],[cDefine23],[cDefine24],[cDefine25],[cDefine26],[cDefine27],[cItemCode],[cItem_class],[cItemName],[cItem_CName],[cFree3],[cFree4],[cFree5],[cFree6],[cFree7],[cFree8],[cFree9],[cFree10],[iInvExchRate],[cUnitID],[ID],[cDefine28],[cDefine29],[cDefine30],[cDefine31],[cDefine32],[cDefine33],[cDefine34],[cDefine35],[cDefine36],[cDefine37],[FPurQuan],[fSaleCost],[fSalePrice],[cQuoCode],[iQuoID],[cSCloser],[dPreMoDate],[iRowNo],[iCusBomID],[iMoQuantity],[cContractID],[cContractTagCode],[cContractRowGuid],[iPPartSeqID],[iPPartID],[iPPartQty],[cCusInvCode],[cCusInvName],[iPreKeepQuantity],[iPreKeepNum],[iPreKeepTotQuantity],[iPreKeepTotNum],[dreleasedate],[fcusminprice],[fimquantity],[fomquantity],[ballpurchase])
values(@ccode,@cinvcode,getdate(),@iquantity,null,@iprice,@iprice/1.17,@iprice,@iquantity*@iprice/1.17,@iquantity*@iprice*0.17,@iquantity*@iprice,0,@iprice/1.17,@iquantity*@iprice/1.17,@iquantity*@iprice*0.17,@iquantity*@iprice,0,100,100,4000,Null,Null,Null,Null,Null,Null,Null,@p2,100,100,@cinvname,17,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,'1','1202',@P1,Null,Null,Null,Null,Null,Null,12000,Null,Null,Null,Null,0,0,Null,Null,Null,getdate(),1,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,Null,'0',Null,Null,'0')
    END      
COMMIT TRAN