SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[HOInterface_CRM_SCAPost_Before]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [HOInterface_CRM_SCAPost_Before] AS' 
END
GO


ALTER PROC [HOInterface_CRM_SCAPost_Before] 
 @XML ntext 
 ,@MsgId int out --0=正常 9=限制、禁止 
 ,@RetMsg varchar(512) out  --返回提醒内容
as
DECLARE @iDOM int, @iret int
Exec @iret = sp_xml_preparedocument @iDOM OUTPUT, @XML
if @iret > 0
BEGIN
   exec sp_xml_removedocument @iDOM	
   RAISERROR('解析XML文档时发生错误，错误号: %d.', 16, 1, @iret) with nowait
   return @iret
END 
--获取操作员基本信息
DECLARE @i int ,
        @Count int ,
        @EmpId int,	 
        @ErrId int,        
        @EmpNo varchar(20),
        @EmpName varchar(20),
        @HostIp varchar(256),
        @HostName varchar(256),
        @UniqueInfo varchar(30),
        @DeptId int,  
        @SCH01 int,  
        @SCI01 int,
        @VAA01 int,
        @ZAE01 int,
        @Value int,
        @SCH11 varchar(64),
        @SCH12 varchar(1024), 
        @SCA05 varchar(20),
		@SCA06 varchar(64), 
        @SCA26 varchar(20),
        @SCA30 varchar(20),  
        @DeptNo varchar(20),
        @DeptName varchar(20),
        @HostMac varchar(256),
        @FileVer varchar(20),
        @OSCA06 varchar(64),
        @OSCA26 varchar(20),
        @OSCA30 varchar(20),
        @SCA01B INT,
        @OSCA01B INT,
        -- 临时变量
        @smsg varchar(30),
        @OSSCA26 varchar(20) 
      
SELECT IDENTITY(int,1,1) AS ID , * INTO #tmpSCA11
  FROM OpenXml(@iDOM,'/Root/SCA1/Ie',8) 
  WITH
  (     
  	    SCA01 int , 
  	    VAA01 int ,            
        SCA04 int ,
        SCA05 varchar(20) ,
        SCA06 varchar(64) ,
        SCA07 varchar(20) ,
        ABBRP varchar(10) ,
        ABBRW varchar(10) ,
        ABW01 varchar(1) ,
        SCA11 datetime ,
        SCA12 int ,
        AAU01 varchar(1) ,
        SCA14 varchar(20) ,
        SCA15 varchar(64) ,
        SCA16 varchar(64) ,
        SCA17 varchar(64) ,
        ACC02 varchar(32) ,
        SCA19 varchar(20) ,
        SCA20 varchar(20) ,
        ABQ02 varchar(32) ,
        SCA22 varchar(20) ,
        AAT02 varchar(64) ,
        SCA24 varchar(64) ,
        SCA25 varchar(20) ,
        SCA26 varchar(20) ,
        SCA27 varchar(64) ,
        SCA28 varchar(30) ,
        SCA29 varchar(128) ,
        SCA30 varchar(20) ,
        SCA31 varchar(20) ,
        SCA32 varchar(255) ,
        SCA33 varchar(255) ,
        SCA39 varchar(20) ,
        VBU01 int,
        SCA01B int,
        BDP02 varchar(100) 
  )
  IF NOT EXISTS(SELECT * from #tmpSCA11)
  BEGIN
    exec sp_xml_removedocument @iDOM	
    RAISERROR('客户档案信息不能为空.' , 16 , 1) WITH NOWAIT 
    RETURN 1 
  END
  

select @OSSCA26 = SCA26 from #tmpSCA11

If isnull(@OSSCA26,'') = ''
BEGIN   
  set @MsgId=0
  set @RetMsg='正常'
  return 0
END
else if isnull(@OSSCA26,'') <> '' 
begin
  --if @OSSCA26  like '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
  IF (LEFT(@OSSCA26, 3) IN ('130','131','132','133','134','135','136','137','147','138','139','150','151','152',
  '153','155','156','157','158','159','176','177','178','181','182','183','184','185','186','187','188','189')
        AND PATINDEX('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]', @OSSCA26) = 1)
  begin
	set @MsgId=0
    set @RetMsg='是手机号'
    return 0 
  end
  else
  begin
    set @MsgId=9
    set @RetMsg='请输入正确的手机号'
    RAISERROR('请输入正确的手机号！！！' , 16 , 1) WITH NOWAIT 
    return 0 
  end
 
end

IF Object_id('tempdb..#tmpSCA11') IS NOT NULL DROP TABLE #tmpSCA11
 



GO