為同一件事寫出十幾個只差幾個參數的存儲過程,是維護上一場噩夢。與其如此,不如把參數打包成一個 XML 字串送進去,在程序內部剖析取用。這麼做的好處很直接:存儲過程的簽章固定下來,COM 元件的外介面也跟著變簡單。
做法並不玄:把參數序列化成 XML 傳入,用 sp_xml_preparedocument 取得 DOM 句柄,再用 OPENXML 對這份 DOM 跑查詢取資料,最後用 sp_xml_removedocument 釋放句柄。因為拿到的是真正的 XML DOM,你甚至可以直接下 XPath 查詢,把多個動作封裝在同一支程序裡。
傳入的 XML 字串:
<root><Customer><customerid>3</customerid><name>Acme Inc.</name></Customer></root>
存儲過程:
CREATE PROCEDURE update_Customer (@XMLdata varchar(8000)) AS
DECLARE @customerid int
DECLARE @customername varchar(50)
DECLARE @XMLdata_id int
EXEC sp_xml_preparedocument @XMLdata_id OUTPUT, @XMLdata, ''
SELECT @customerid = customerid,
@customername = [name]
FROM OPENXML(@XMLdata_id, '//Customer', 2)
WITH (customerid int, [name] varchar(50))
EXEC sp_xml_removedocument @XMLdata_id
UPDATE Customer
SET Customer.[name] = ISNULL(@customername, Customer.[name])
WHERE Customer.tblID = @customerid
幾個參數需要留意。第一參數回傳的是 DOM 文件句柄;第二參數是 XML 來源;OPENXML 的第二個參數是節點的 XPath 對應位置,第三個參數填 2 表示採用「以元素為中心」的對應方式;WITH 子句則定義剖析結果的列集(rowset)欄位格式。最後一定要呼叫 sp_xml_removedocument,否則句柄會一直佔著資源。
這次傳入的是一串 customerid:
<root>
<Customer><customerid>1</customerid></Customer>
<Customer><customerid>2</customerid></Customer>
<Customer><customerid>3</customerid></Customer>
</root>
程序主體幾乎沒變,只是把 SELECT 換成 DELETE:
EXEC sp_xml_preparedocument @xml_id OUTPUT, @XMLdata, ''
DELETE FROM Customer
WHERE Customer.tblID IN (
SELECT customerid
FROM OPENXML(@xml_id, '//Customer', 2)
WITH (customerid int))
過去要達成同樣效果,得在 ADO 端拼字串,或者反覆呼叫同一支程序。前者容易出錯又難除錯,後者會把網路來回次數乘上筆數。改用 XML 參數之後,一次往返解決,流量也省下來。
SQL Server 2000 讓這件事做得比以往順手,但有個硬限制必須記住:當成參數傳送的 XML 被限制在 8,000 字元以內。資料量大的批次作業,還是要切段處理或改用別的通道。另外,sp_xml_preparedocument、OPENXML、sp_xml_removedocument 的完整說明在 MSDN Library 都有,動手前值得先讀一遍。