SQL校验优化

 

我的思路只能查当前的：

----校验此行订单是否已导入，若已导入则提示订单号并Return

--        IF EXISTS (SELECT 1 FROM DOC_Order_Header b LEFT JOIN @tblData a

--                         ON

--                         a.ConsigneeID = b.ConsigneeID

--                         AND a.ConsigneeID = b.ConsigneeID

--                               AND a.SOReference1 = b.SOReference1

--                               AND a.H_EDI_01 = b.H_EDI_01

--                               AND a.OrderTime = b.OrderTime

--                               AND a.H_EDI_02 = b.H_EDI_02

--                               AND a.HeaderNotes = b.Notes

--                               WHERE b.CustomerID = 'KKKL'

--                         )

--        BEGIN

--        SET @Msg = @Msg + '订单号：' +  @sSOReference1 + '已存在，请检查数据！'

--        RETURN -1                

--        END

优化思路：

 

--校验客户是否存在，返回所有不存在的客户编号：        

  SET @sConsigneeID = NULL

  SELECT @sConsigneeID = ISNULL(@sConsigneeID + ',', '') + ConsigneeID

  FROM (

    SELECT DISTINCT a.ConsigneeID AS ConsigneeID

    FROM @tblAllData a

      LEFT JOIN BAS_Customer co ON a.ConsigneeID = co.CustomerID AND co.Customer_Type = 'CO'

    WHERE

      co.CustomerID IS NULL ) a

  IF @sConsigneeID <> ''

    SET @Msg = @Msg + '客户档案不存在：' + @sConsigneeID

   

  --校验订单是否存在，返回所有已存在的订单号

    SET @sSOReference1 = NULL

  SELECT @sSOReference1 = ISNULL(@sSOReference1 + ',', '') + SOReference1

  FROM (

    SELECT DISTINCT a.SOReference1 AS SOReference1

    FROM @tblAllData a

      LEFT JOIN DOC_Order_Header oh ON a.SOReference1 = oh.SOReference1 AND oh.CustomerID = 'KKKL'

    WHERE

      oh.SOReference1 IS NOT NULL ) a

  IF @sSOReference1 <> ''

    SET @Msg = @Msg + '订单已存在：' + @sSOReference1

Ps 创建临时表  CREATE TABLE #TEMP 加两个##表示系统级别的临时表