CREATE PROCEDURE [dbo].[p_zdbk_crf5](
@xm VARCHAR(10)='客房',
@crcjfsj VARCHAR(8)='12:00:00', --次日加收半天费时间
@czy VARCHAR(10)='服务器',
@ccgcfhz INT OUTPUT, --存储过程返回值
@ccgcfhzms NVARCHAR(100) OUTPUT --存储过程返回值描述
)AS DECLARE @zdid VARCHAR(20)
DECLARE @gkid VARCHAR(20)
DECLARE @bh VARCHAR(10)
DECLARE @mc VARCHAR(10)
DECLARE @jrsj DATETIME
DECLARE @dzl DECIMAL
DECLARE @dze DECIMAL
DECLARE @cjffwxmid VARCHAR(10)
DECLARE @ycjfsj DATETIME
DECLARE @jffs VARCHAR(10)
SET @jffs=''
IF @dzl<>0 OR @dze<>0
SET @jffs='FPKDSZK'
--取超时宾客
DECLARE curZdbk CURSOR FOR
SELECT zdid,gkid,xm,bh,mc,jrsj,dzl,dze,cjffwxmid,ycjfsj FROM zdbkbdfwxm WHERE crcjf=1 AND crcjfsj=@crcjfsj --AND xm=@xm
OPEN curZdbk
FETCH NEXT FROM curZdbk
INTO @zdid,@gkid,@xm,@bh,@mc,@jrsj,@dzl,@dze,@cjffwxmid,@ycjfsj
WHILE (@@FETCH_STATUS=0) BEGIN DECLARE @yyrq DATETIME
SET @yyrq=CAST(CONVERT( VARCHAR(12) , GETDATE(), 102 ) AS DATETIME)
DECLARE @xfsj DATETIME
SET @xfsj=CAST(CONVERT( VARCHAR(12) , GETDATE(), 102 )+' '+@crcjfsj AS DATETIME)
DECLARE @yjtzjssj DATETIME
SET @yjtzjssj=DATEADD( DAY, 1 , CAST(CONVERT(VARCHAR(12) , GETDATE(), 102 )+' 12:00:00' AS DATETIME))
DECLARE @fl VARCHAR(10)
DECLARE @zl VARCHAR(10)
DECLARE @xmmc VARCHAR(30)
DECLARE @dw VARCHAR(2)
DECLARE @dj DECIMAL
DECLARE @sl DECIMAL
SET @sl=0.5
DECLARE @xj DECIMAL
DECLARE @ys DECIMAL
DECLARE @yxdz BIT
DECLARE @jzdz BIT
DECLARE @zdjr BIT
DECLARE @zdjs BIT
DECLARE @sc DECIMAL
DECLARE @tzjssl DECIMAL
DECLARE @djcpbm BIT
DECLARE @tcxm BIT
DECLARE @jsfw BIT
DECLARE @cpddy BIT
DECLARE @fjfw BIT
DECLARE @kgxm BIT
DECLARE @zsxm BIT
--取超时宾客加收费项目
SELECT @fl=fwxmfl,@zl=zl,@xmmc=xmmc,@dw=dw,@dj=dj,@yxdz=yxdz,@jzdz=jzdz,@zdjr=zdjr,@zdjs=zdjs,@sc=sc,@tzjssl=tzjssl,@djcpbm=djcpbm,@tcxm=tcxm,@jsfw=jsfw,@cpddy=cpddy,@fjfw=fjfw,@kgxm=kgxm,@zsxm=zsxm FROM g_sy_fwxm WHERE fwxmid=@cjffwxmid
IF @jffs='' AND @jzdz=1
SET @jffs='JZDZ'
IF @jffs='' AND @zsxm=1
SET @jffs='赠送'
SET @xj=@dj*@sl
SET @ys=@xj
IF @dze<>0
SET @dze=@dze*@sl
SET @ys=@xj-@dze
IF @dzl<>0
SET @ys=@xj*@dzl
SET @dze=@xj-@ys
IF @xmmc IS NOT NULL
--写入消费明细
DECLARE @xfmxid varchar(20)
SET @xfmxid=SUBSTRING(CONVERT(VARCHAR, GETDATE(), 126),3,2)+SUBSTRING(CONVERT(VARCHAR, GETDATE(),126),6,18)
INSERT INTO g_sy_xfmx (xfmxid,zdid,gkid,xm,bh,mc,fl,zl,xmmc,dw,dj0,dj,sl,xj,dzl,dze,ys,czy,yxdz,jzdz,zdjr,zdjs,sc,tzjssl,xfrq,xfsj,yjtzjssj,ksjs,ksjssj,ksjsczy,djcpbm,jffs,tcxm,fjfw) VALUES (@xfmxid,@zdid,@gkid,@xm,@bh,@mc,@fl,@zl,@xmmc,@dw,@dj,@dj,@sl,@xj,0,0,@ys,@czy,@yxdz,@jzdz,@zdjr,@zdjs,@sc,@tzjssl,@yyrq,@xfsj,@yjtzjssj,1,@xfsj,@czy,@djcpbm,@jffs,@tcxm,@fjfw)
IF @@ERROR<>0
BEGIN
SET @ccgcfhz=0
SET @ccgcfhzms='未成功写入半天房费消费明细'
END
--更新宾客已重计费时间
UPDATE g_sy_xfjs SET ycjfsj=getdate()
IF @@ERROR<>0
BEGIN
SET @ccgcfhz=0
SET @ccgcfhzms='未成功更新宾客已重计费时间'
END END CLOSE curZdbk
DEALLOCATE curZdbk SET @ccgcfhz=1
SET @ccgcfhzms=''
GO
@xm VARCHAR(10)='客房',
@crcjfsj VARCHAR(8)='12:00:00', --次日加收半天费时间
@czy VARCHAR(10)='服务器',
@ccgcfhz INT OUTPUT, --存储过程返回值
@ccgcfhzms NVARCHAR(100) OUTPUT --存储过程返回值描述
)AS DECLARE @zdid VARCHAR(20)
DECLARE @gkid VARCHAR(20)
DECLARE @bh VARCHAR(10)
DECLARE @mc VARCHAR(10)
DECLARE @jrsj DATETIME
DECLARE @dzl DECIMAL
DECLARE @dze DECIMAL
DECLARE @cjffwxmid VARCHAR(10)
DECLARE @ycjfsj DATETIME
DECLARE @jffs VARCHAR(10)
SET @jffs=''
IF @dzl<>0 OR @dze<>0
SET @jffs='FPKDSZK'
--取超时宾客
DECLARE curZdbk CURSOR FOR
SELECT zdid,gkid,xm,bh,mc,jrsj,dzl,dze,cjffwxmid,ycjfsj FROM zdbkbdfwxm WHERE crcjf=1 AND crcjfsj=@crcjfsj --AND xm=@xm
OPEN curZdbk
FETCH NEXT FROM curZdbk
INTO @zdid,@gkid,@xm,@bh,@mc,@jrsj,@dzl,@dze,@cjffwxmid,@ycjfsj
WHILE (@@FETCH_STATUS=0) BEGIN DECLARE @yyrq DATETIME
SET @yyrq=CAST(CONVERT( VARCHAR(12) , GETDATE(), 102 ) AS DATETIME)
DECLARE @xfsj DATETIME
SET @xfsj=CAST(CONVERT( VARCHAR(12) , GETDATE(), 102 )+' '+@crcjfsj AS DATETIME)
DECLARE @yjtzjssj DATETIME
SET @yjtzjssj=DATEADD( DAY, 1 , CAST(CONVERT(VARCHAR(12) , GETDATE(), 102 )+' 12:00:00' AS DATETIME))
DECLARE @fl VARCHAR(10)
DECLARE @zl VARCHAR(10)
DECLARE @xmmc VARCHAR(30)
DECLARE @dw VARCHAR(2)
DECLARE @dj DECIMAL
DECLARE @sl DECIMAL
SET @sl=0.5
DECLARE @xj DECIMAL
DECLARE @ys DECIMAL
DECLARE @yxdz BIT
DECLARE @jzdz BIT
DECLARE @zdjr BIT
DECLARE @zdjs BIT
DECLARE @sc DECIMAL
DECLARE @tzjssl DECIMAL
DECLARE @djcpbm BIT
DECLARE @tcxm BIT
DECLARE @jsfw BIT
DECLARE @cpddy BIT
DECLARE @fjfw BIT
DECLARE @kgxm BIT
DECLARE @zsxm BIT
--取超时宾客加收费项目
SELECT @fl=fwxmfl,@zl=zl,@xmmc=xmmc,@dw=dw,@dj=dj,@yxdz=yxdz,@jzdz=jzdz,@zdjr=zdjr,@zdjs=zdjs,@sc=sc,@tzjssl=tzjssl,@djcpbm=djcpbm,@tcxm=tcxm,@jsfw=jsfw,@cpddy=cpddy,@fjfw=fjfw,@kgxm=kgxm,@zsxm=zsxm FROM g_sy_fwxm WHERE fwxmid=@cjffwxmid
IF @jffs='' AND @jzdz=1
SET @jffs='JZDZ'
IF @jffs='' AND @zsxm=1
SET @jffs='赠送'
SET @xj=@dj*@sl
SET @ys=@xj
IF @dze<>0
SET @dze=@dze*@sl
SET @ys=@xj-@dze
IF @dzl<>0
SET @ys=@xj*@dzl
SET @dze=@xj-@ys
IF @xmmc IS NOT NULL
--写入消费明细
DECLARE @xfmxid varchar(20)
SET @xfmxid=SUBSTRING(CONVERT(VARCHAR, GETDATE(), 126),3,2)+SUBSTRING(CONVERT(VARCHAR, GETDATE(),126),6,18)
INSERT INTO g_sy_xfmx (xfmxid,zdid,gkid,xm,bh,mc,fl,zl,xmmc,dw,dj0,dj,sl,xj,dzl,dze,ys,czy,yxdz,jzdz,zdjr,zdjs,sc,tzjssl,xfrq,xfsj,yjtzjssj,ksjs,ksjssj,ksjsczy,djcpbm,jffs,tcxm,fjfw) VALUES (@xfmxid,@zdid,@gkid,@xm,@bh,@mc,@fl,@zl,@xmmc,@dw,@dj,@dj,@sl,@xj,0,0,@ys,@czy,@yxdz,@jzdz,@zdjr,@zdjs,@sc,@tzjssl,@yyrq,@xfsj,@yjtzjssj,1,@xfsj,@czy,@djcpbm,@jffs,@tcxm,@fjfw)
IF @@ERROR<>0
BEGIN
SET @ccgcfhz=0
SET @ccgcfhzms='未成功写入半天房费消费明细'
END
--更新宾客已重计费时间
UPDATE g_sy_xfjs SET ycjfsj=getdate()
IF @@ERROR<>0
BEGIN
SET @ccgcfhz=0
SET @ccgcfhzms='未成功更新宾客已重计费时间'
END END CLOSE curZdbk
DEALLOCATE curZdbk SET @ccgcfhz=1
SET @ccgcfhzms=''
GO
fetch ...
while @@fetch_status=0
begin
/**你的code**/
-- 加以下这句
fetch next from c1 into ......endclose c1
deallocate c1