暂告一段落

最新推荐文章于 2023-11-24 12:09:24 发布

深思千年

最新推荐文章于 2023-11-24 12:09:24 发布

阅读量1k

点赞数

文章标签： insert null object date

本文链接：https://blog.csdn.net/ylchou/article/details/7284105

版权

USE [hb_lottery]
GO
/****** Object: StoredProcedure [dbo].[dis_insertBackStation]    Script Date: 02/22/2012 17:49:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[dis_insertBackStation]
    --@ProductId_Array varChar(800),
    --@ModuleId int
    @delivery varchar(50), --@usid bigint,
   @postationid varchar(50)    --@stid varchar(50)
AS

    DECLARE @stid varchar(50),@usid varchar(50),@puid varchar(50)

    begin transaction
    --Set NoCount ON
    --delete from ProductListSpecial where ModuleId=@ModuleId
    select @usid = usid from usr_users where username = @delivery --'shoulijun'
   select @stid = stid from sta_station where postationid = @postationid   --5400010240-- postationid --5480011708
   --select @puid = puid from dis_war where
   select @puid = puid from dis_pickup where usid = @usid

    insert into dis_backstation (bsid,puid,stid,usid,totalmoney,outcash,outmort,incash,ivt,inconvert,createtime) values(NEWID(),@puid,@stid,@usid,0,0,0,0,0,0,GETDATE())

    if @@error=0
    begin
        commit transaction
    end
    else
    begin
        rollback transaction
    end

select 'ok!'

USE [hb_lottery]
GO
/****** Object: StoredProcedure [dbo].[dis_insertWari]    Script Date: 02/22/2012 17:48:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[dis_insertWari]
    @delivery varchar(50), --@usid bigint,
   @postationid varchar(50),    --@stid varchar(50)
   @sn varchar(500)
AS
    DECLARE @PointerPrev int
    DECLARE @PointerCurr int
    DECLARE @TSn varchar(50)
    DECLARE @usid bigint,@stid varchar(50),@waid varchar(50),@dgid varchar(50),@puid varchar(50)--,@status tinyint

    select @usid = usid from usr_users where username = @delivery --'shoulijun'
   select @stid = stid from sta_station where @postationid = @postationid   --5400010240-- postationid --5480011708
   --select @waid = waid,@dgid = dgid from dis_war where status = 2 and sn = @sn --3500950151246
   select @puid = puid from dis_pickup where usid = @usid

    Set @PointerPrev=1
    set @PointerCurr=1

    begin transaction
    Set NoCount ON

    --first
    Set @PointerCurr=CharIndex('#',@sn,@PointerPrev+1)
    set @TSn=SUBSTRING(@sn,@PointerPrev,@PointerCurr-@PointerPrev)
            select @waid = waid,@dgid = dgid from dis_war where status = 2 and sn = @TSn --3500950151246
            --根据@waid和@dgid是否为空判断是否有记录当且仅当二者都非空时执行插入语句
           if (@waid is not null and @dgid is not null)
           begin
               insert into dis_warin (swid,waid,dgid,stid,sn,createtime,inputid,deliverid,puid) values(NEWID(),@waid,@dgid,@stid,@TSn,GETDATE(),@usid,@usid,@puid)
           end

    --second
    SET @PointerPrev = @PointerCurr
    while (@PointerPrev+1 < LEN(@sn))
    Begin
        Set @PointerCurr=CharIndex('#',@sn,@PointerPrev+1)
        if(@PointerCurr>0)
        Begin
            set @TSn=SUBSTRING(@sn,@PointerPrev+1,@PointerCurr-@PointerPrev-1)
            select @waid = waid,@dgid = dgid from dis_war where status = 2 and sn = @TSn --3500950151246
            --根据@waid和@dgid是否为空判断是否有记录当且仅当二者都非空时执行插入语句
           if (@waid is not null and @dgid is not null)
           begin
               insert into dis_warin (swid,waid,dgid,stid,sn,createtime,inputid,deliverid,puid) values(NEWID(),@waid,@dgid,@stid,@TSn,GETDATE(),@usid,@usid,@puid)
           end
            SET @PointerPrev = @PointerCurr
        End
        else
            Break
    End

    --third
    set @TSn=SUBSTRING(@sn,@PointerPrev+1,LEN(@sn)-@PointerPrev)
            select @waid = waid,@dgid = dgid from dis_war where status = 2 and sn = @TSn --3500950151246
            --根据@waid和@dgid是否为空判断是否有记录当且仅当二者都非空时执行插入语句
           if (@waid is not null and @dgid is not null)
           begin
               insert into dis_warin (swid,waid,dgid,stid,sn,createtime,inputid,deliverid,puid) values(NEWID(),@waid,@dgid,@stid,@TSn,GETDATE(),@usid,@usid,@puid)
           end
    Set NoCount OFF
    if @@error=0
    begin
        commit transaction
    end
    else
    begin
        rollback transaction
    end
    select 'ok'