2007年12月2日 星期日

[SQL2005]自創SQL篩選不組字串查詢方式

在寫SQL句時,一個Stored Procedure,傳入一個參數是一群資料的集合作為篩選條件
這時常常會用組字串的方式,使用 IN 的子句當作 WHERE條件,例如下面這個例子:
CREATE PROC GetProjects

(

@ProjectList nvarchar(max)

)

AS

BEGIN

DECLARE @sql nvarchar(max)

SET @sql = 'SELECT * FROM Project WHERE projectSn IN (' + @ProjectList +')'

EXEC sp_executesql @sql

END
上面的例子,呼叫時傳入 "1,2,3" 這樣的字串當作 @ProjectList,
就可以找出Project資料表中,ProjectSn為1,2,3的資料
但這方法就是透過組字串來執行

在SQL2005增加了xml的xQuery方法,可以將xml變成關聯式資料,於是我修改上面的句子,變成:
CREATE PROC GetProjects

(

@ProjectList nvarchar(max)

)

AS

BEGIN

DECLARE @xml xml

SET @xml = @ProjectList;

WITH LIST AS

(

SELECT N.value('./text()[1]', 'int') ProjectSn FROM @xml.nodes('/r/n') V(N)

)

SELECT * FROM Project

WHERE

(

@ProjectList = ''

OR

EXISTS (SELECT TOP 1 * FROM Project.ProjectSn = List.ProjectSn)

)

END
在上面的例子,同樣的用法,
將@ProjectList的傳入值改成 "<r><n>1</n><n>2</n><n>3</n></r>",
就可以得到和上面的例子一樣的結果

使用組字串的方式,SQL句無法先編譯,且無法進行句子的檢查,
用第二種寫法,如果欄位有錯比較容易找到錯誤,而且SQL句預先編譯,效能上應該會比較好
不過傳的東西多了一點,因為SQL一直不直援陣列,真討厭
有關SQL2005 xQuery用法,請參閱相關書籍

0 回應:

 
TEMPLATE HACKS AND TWEAKS BY [ METAMUSE ] BLACKCAT 1.1