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 加兩個##表示系統級別的臨時表