回應範例
{
"customer_data": [
{
"customer_no": "CUST001",
"customer_shortname": "測試客戶",
"customer_name": "測試客戶股份有限公司",
"modify_no": "USER001",
"tran_status": "AU"
}
]
}
資料庫更新 SQL
判斷新增或修改
SELECT COUNT(1) FROM CUSTOMER WHERE ID = N'{customer_no}'
-- count > 0 → UPDATE, count = 0 → INSERT
INSERT（新增客戶）
INSERT INTO CUSTOMER (
ID, SHOTNAME, NAME, TEL, FAX, EMAIL,
AREA_ID, AREA_NAME, ADDRESS,
CHECKMAINTAINNO, LASTMAINTAINUSER, LASTMAINTAINDATETIME
) VALUES (
:CUSTOMERID,
:SHOTNAME,
:CUSTOMER_NAME,
:TEL,
:FAX,
:EMAIL,
:AREA_ID,
:AREA_NAME,
:ADDRESS,
1,
:Creator,
GETDATE()
)
UPDATE（修改客戶）
UPDATE CUSTOMER SET
SHOTNAME = :SHOTNAME,
NAME = :CUSTOMER_NAME,
TEL = :TEL,
FAX = :FAX,
EMAIL = :EMAIL,
AREA_ID = :AREA_ID,
AREA_NAME = :AREA_NAME,
ADDRESS = :ADDRESS
WHERE ID = :CUSTOMERID