[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用法，請參閱相關書籍