select ltrim(rtrim(yhName)) from yh --去掉字符串前后的空格select replace(yhName,' ','') from yh --去掉字符串中所有的空格
select ltrim(rtrim(yhName)) from yh
Create Procedure OutPutStorage As Declare @Xfdm char(8),@o_no Char(8) Declare @No_ Numeric(9) ,@Sele_tag Char(1),@wzdm Char(30),@ckdm Char(6), @o_Date DateTime,@o_sl Numeric(9),@O_Danj Numeric(9,2),@O_Jine Numeric(9,2),@O_zk Numeric(5), @O_zke Numeric(9,2),@Shui_l Numeric(5),@Shui Numeric(9,2),@fkdm Char(4),@fkfs Char(4), @Bepay Numeric(9,2),@NotPay Numeric(9,2),@Czdm Char(10),@ywman Char(8),@fp_no Char(8), @Dj Numeric(9,2),@In_Jine Numeric(9,2),@ml Numeric(9,2),@bz Char(255) Declare @mBepay Numeric(9,2) Delete m_Out Where o_no in (Select Distinct o_no from Out) or ckdm not in (Select dm as ckdm from dm_ck) Declare cClient Cursor For Select Xfdm from m_Out where Xfdm not in (Select dm as xfdm from dm_kh) Begin Transaction Open cClient Fetch cClient Into @Xfdm While @@Fetch_Status=0 Begin Insert Dm_kh(dm) Values(@Xfdm) Fetch cClient Into @Xfdm End Close cClient Commit Transaction deAllocate cClient
Declare cMain Cursor For Select Distinct O_no from m_Out Begin Transaction Open cMain Fetch cMain Into @O_no While @@Fetch_Status=0 Begin Declare cDetail Cursor For Select No_,Sele_tag,xfdm,wzdm,ckdm,o_no,o_Date,o_sl,O_Danj,O_Jine,O_zk,O_zke,Shui_l, Shui,fkdm,fkfs,Bepay,NotPay,Czdm,ywman,fp_no,Dj,In_Jine,ml,bz from m_Out Where o_no=@o_no Begin Transaction Open cDetail Fetch cDetail Into @No_,@Sele_tag,@xfdm,@wzdm,@ckdm,@o_no,@o_Date,@o_sl,@O_Danj,@O_Jine,@O_zk,@O_zke, @Shui_l,@Shui,@fkdm,@fkfs,@Bepay,@NotPay,@Czdm,@ywman,@fp_no,@Dj,@In_Jine,@ml,@bz While @@Fetch_Status=0 Begin Begin Transaction insert Out ( No_,Sele_tag,xfdm,wzdm,ckdm,o_no,o_Date,o_sl,O_Danj,O_Jine,O_zk,O_zke,Shui_l, Shui,fkdm,fkfs,Bepay,NotPay,Czdm,ywman,fp_no,Dj,In_Jine,ml,bz) Values( @No_,@Sele_tag,@xfdm,@wzdm,@ckdm,@o_no,@o_Date,@o_sl,@O_Danj,@O_Jine,@O_zk,@O_zke, @Shui_l,@Shui,@fkdm,@fkfs,@Bepay,@NotPay,@Czdm,@ywman,@fp_no,@Dj,@In_Jine,@ml,@bz) If @fkdm='即结' Select @bepay=@o_zke Else Select @bepay=0.00
If Exists(Select * from cun where wzdm=@wzdm and ckdm=@ckdm) Update cun Set Sl=@o_sl-sl where wzdm=@wzdm and ckdm=@ckdm Else Insert cun(wzdm,ckdm,sl) Values(@wzdm,@ckdm,0-@o_sl) Commit Transaction Fetch cDetail Into @No_,@Sele_tag,@xfdm,@wzdm,@ckdm,@o_no,@o_Date,@o_sl,@O_Danj,@O_Jine,@O_zk,@O_zke, @Shui_l,@Shui,@fkdm,@fkfs,@Bepay,@NotPay,@Czdm,@ywman,@fp_no,@Dj,@In_Jine,@ml,@bz End Close cDetail Open cDetail Fetch cDetail Into @No_,@Sele_tag,@xfdm,@wzdm,@ckdm,@o_no,@o_Date,@o_sl,@O_Danj,@O_Jine,@O_zk,@O_zke, @Shui_l,@Shui,@fkdm,@fkfs,@Bepay,@NotPay,@Czdm,@ywman,@fp_no,@Dj,@In_Jine,@ml,@bz If @@Fetch_Status=0 Begin If @fkdm='预付' Begin Insert dm_ys(fp_no,o_no,o_Date,wzdm,o_sl,o_danj,O_Jine,o_zk,O_zke, Shui_l,shui,xfdm,fkdm,fkfs,NotPay_L,Bepay,NotPay,czdm,bz,YwMan) Values(@fp_no,@O_no,@O_Date,'',0,0,0,@O_zk,0, @Shui_l,@shui,@Xfdm,@fkdm,@fkfs,0,@Bepay,@NotPay,@czdm,'预付款',@YwMan) End End Close cDetail Commit Transaction Deallocate cDetail Fetch cMain Into @o_no End Close cMain Commit Transaction DeAllocate cMain
As
Declare @Xfdm char(8),@o_no Char(8)
Declare @No_ Numeric(9) ,@Sele_tag Char(1),@wzdm Char(30),@ckdm Char(6),
@o_Date DateTime,@o_sl Numeric(9),@O_Danj Numeric(9,2),@O_Jine Numeric(9,2),@O_zk Numeric(5),
@O_zke Numeric(9,2),@Shui_l Numeric(5),@Shui Numeric(9,2),@fkdm Char(4),@fkfs Char(4),
@Bepay Numeric(9,2),@NotPay Numeric(9,2),@Czdm Char(10),@ywman Char(8),@fp_no Char(8),
@Dj Numeric(9,2),@In_Jine Numeric(9,2),@ml Numeric(9,2),@bz Char(255)
Declare @mBepay Numeric(9,2) Delete m_Out Where o_no in (Select Distinct o_no from Out)
or ckdm not in (Select dm as ckdm from dm_ck)
Declare cClient Cursor For
Select Xfdm from m_Out where Xfdm not in (Select dm as xfdm from dm_kh)
Begin Transaction
Open cClient
Fetch cClient Into @Xfdm
While @@Fetch_Status=0
Begin
Insert Dm_kh(dm) Values(@Xfdm)
Fetch cClient Into @Xfdm
End
Close cClient
Commit Transaction
deAllocate cClient
Declare cMain Cursor For
Select Distinct O_no from m_Out
Begin Transaction
Open cMain
Fetch cMain Into @O_no
While @@Fetch_Status=0
Begin
Declare cDetail Cursor For
Select No_,Sele_tag,xfdm,wzdm,ckdm,o_no,o_Date,o_sl,O_Danj,O_Jine,O_zk,O_zke,Shui_l,
Shui,fkdm,fkfs,Bepay,NotPay,Czdm,ywman,fp_no,Dj,In_Jine,ml,bz
from m_Out
Where o_no=@o_no
Begin Transaction
Open cDetail
Fetch cDetail Into @No_,@Sele_tag,@xfdm,@wzdm,@ckdm,@o_no,@o_Date,@o_sl,@O_Danj,@O_Jine,@O_zk,@O_zke,
@Shui_l,@Shui,@fkdm,@fkfs,@Bepay,@NotPay,@Czdm,@ywman,@fp_no,@Dj,@In_Jine,@ml,@bz
While @@Fetch_Status=0
Begin
Begin Transaction
insert Out ( No_,Sele_tag,xfdm,wzdm,ckdm,o_no,o_Date,o_sl,O_Danj,O_Jine,O_zk,O_zke,Shui_l,
Shui,fkdm,fkfs,Bepay,NotPay,Czdm,ywman,fp_no,Dj,In_Jine,ml,bz)
Values( @No_,@Sele_tag,@xfdm,@wzdm,@ckdm,@o_no,@o_Date,@o_sl,@O_Danj,@O_Jine,@O_zk,@O_zke,
@Shui_l,@Shui,@fkdm,@fkfs,@Bepay,@NotPay,@Czdm,@ywman,@fp_no,@Dj,@In_Jine,@ml,@bz)
If @fkdm='即结'
Select @bepay=@o_zke
Else
Select @bepay=0.00
Insert dm_ys(fp_no,o_no,o_Date,wzdm,o_sl,o_danj,O_Jine,o_zk,O_zke,
Shui_l,shui,xfdm,fkdm,fkfs,NotPay_L,Bepay,NotPay,czdm,bz,YwMan)
Values(@fp_no,@O_no,@O_Date,@wzdm,@O_sl,@O_danj,@o_Jine,@O_zk,@O_zke,
@Shui_l,@shui,@Xfdm,@fkdm,@fkfs,0,@Bepay,@O_zke-@Bepay,@czdm,@bz,@YwMan)
Insert Ico(O_Date,O_no,wzdm,ckdm,O_sl,O_danj,O_jine,O_zk,O_zke)
Values(@O_Date,@O_no,@Wzdm,@ckdm,@O_sl,@O_danj,@O_Jine,@O_zk,@In_Jine)
If Exists(Select * from cun where wzdm=@wzdm and ckdm=@ckdm)
Update cun Set Sl=@o_sl-sl where wzdm=@wzdm and ckdm=@ckdm
Else
Insert cun(wzdm,ckdm,sl) Values(@wzdm,@ckdm,0-@o_sl)
Commit Transaction
Fetch cDetail Into @No_,@Sele_tag,@xfdm,@wzdm,@ckdm,@o_no,@o_Date,@o_sl,@O_Danj,@O_Jine,@O_zk,@O_zke,
@Shui_l,@Shui,@fkdm,@fkfs,@Bepay,@NotPay,@Czdm,@ywman,@fp_no,@Dj,@In_Jine,@ml,@bz
End
Close cDetail
Open cDetail
Fetch cDetail Into @No_,@Sele_tag,@xfdm,@wzdm,@ckdm,@o_no,@o_Date,@o_sl,@O_Danj,@O_Jine,@O_zk,@O_zke,
@Shui_l,@Shui,@fkdm,@fkfs,@Bepay,@NotPay,@Czdm,@ywman,@fp_no,@Dj,@In_Jine,@ml,@bz
If @@Fetch_Status=0
Begin
If @fkdm='预付'
Begin
Insert dm_ys(fp_no,o_no,o_Date,wzdm,o_sl,o_danj,O_Jine,o_zk,O_zke,
Shui_l,shui,xfdm,fkdm,fkfs,NotPay_L,Bepay,NotPay,czdm,bz,YwMan)
Values(@fp_no,@O_no,@O_Date,'',0,0,0,@O_zk,0,
@Shui_l,@shui,@Xfdm,@fkdm,@fkfs,0,@Bepay,@NotPay,@czdm,'预付款',@YwMan)
End
End
Close cDetail
Commit Transaction
Deallocate cDetail
Fetch cMain Into @o_no
End Close cMain
Commit Transaction
DeAllocate cMain