导入人与门店挂靠关系

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

 

--业务员对应门店
ALTER PROC [dbo].[Pipstoreuser]
@XwUserNumber int
  
AS
    DECLARE @Count INT  
    SET  @Count = 0
    DECLARE @curId INT
    DECLARE cur CURSOR        --定义一个游标
        READ_ONLY
    FOR
--        SELECT  StoreID FROM dbo.TBasStore WHERE SaleAreaID = @saleareaid
        SELECT id FROM tempStoreUser WHERE 用户E号 = @XwUserNumber
       
      --指定游标
    OPEN cur-- 打开游标
    FETCH NEXT FROM cur INTO @curId --把提取操作的列数据放到局部变量中
    WHILE ( @@fetch_status = 0 )--返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态
        BEGIN
       
                DECLARE @username VARCHAR(500)     --姓名
                DECLARE @userid INT                --用户id
                DECLARE @storeid INT               --门店id
                DECLARE @storecode VARCHAR(50)     --门店编号
               
                DECLARE @tExceptionInfo NVARCHAR(2000)
    DECLARE @tExceptionCode INT        
    SET @tExceptionInfo=''
    SET @tExceptionCode= 0   
               
    SELECT @username = 姓名,@storecode = 门店编码  from  tempStoreUser where id=@curId
    
    
     --门店编号数据合法性验证
                    SET @storeid =0
                    SELECT  @storeid = storeid
                    FROM    dbo.TBasStore
                    WHERE   StoreCode= LTRIM(RTRIM(@storecode))
                    IF @storeid=0
                        BEGIN 
       SET     @tExceptionInfo = @tExceptionInfo
                               + '门店编号不存在;'
                        END
                       
                   --业务员数据合法性验证
                    SET @userid =0
                    SELECT  @userid = userid
                    FROM    dbo.TBasuserinfo
                    WHERE   username= LTRIM(RTRIM(@userName))
                    IF @userid=0
                        BEGIN 
       SET     @tExceptionInfo = @tExceptionInfo
                               + '业务员不存在;'
                        END
 
 
                    IF @storeid = 0 OR @userid = 0
                    BEGIN
                         UPDATE  tempStoreUser
       SET     结果编码 = 106 ,
         结果信息 = @tExceptionInfo
       WHERE   id = @curId 
                    END
                   
                    ELSE
                    BEGIN
                          --插入门店和业务员关系
       if not exists(select Userid,StoreID from TPlaAssignStore where UserID=@UserID and StoreID=@StoreID)
        begin
         insert into TPlaAssignStore(Userid,StoreID,RegionID,AssignTypeID,aabb)
               values(@UserID,@StoreID,1,1,217489)
        end
       ELSE
           BEGIN
               UPDATE dbo.TPlaAssignStore SET updtime = GETDATE(),aabb = 217489
               WHERE UserID = @userid AND StoreID = @storeid
           end 
        
       SET @Count = @Count +1  
                    end
                                                               

                         
            --提前下一位信息
            FETCH NEXT FROM cur INTO @curId
   
        END
         SELECT  @Count
    CLOSE cur--关闭游标
    DEALLOCATE cur
--删除游标