用非动态SQL语句在SQL Server中动态查询

　当前位置：首页 >> 网管 >> 数据库 >> SQL Server >> 用非动态SQL语句在SQL Server中动态查询

来源：　作者：　发布时间：2008-09-10

　　问题：

　　我尝试在一个存储过程中传递一系列以逗号划定界限的值，以限制结果集。但是无论什么时候，我在IN子句中使用变量，都会得到错误信息。是否存在一种不执行动态SQL语句也能完成查询的方式呢?

　　专家解答：

　　这里存在一种不执行动态SQL语句也能完成查询的方式，但是首先让我们来探究这个问题。我将在以下例子中运用AdventureWorks数据库。

　　在你只有一个值的时候，执行将不会有什么问题。

　　Declare @ManagerIDs Varchar(100)
　　Set @ManagerIDs = '3'
　　Select * from HumanResources.Employee
　　Where ManagerID IN (@ManagerIDs)

　　但是一旦你增加逗号，结果就会大致如下：

　　Declare @ManagerIDs Varchar(100)
　　Set @ManagerIDs = '3,6'
　　Select * from HumanResources.Employee
　　Where ManagerID IN (@ManagerIDs)
　　Msg 245, Level 16, State 1, Line 4
　　Conversion failed when converting the varchar value '3,6' to data type int.

　　这是因为SQL Sever分辨出ManagerID列是一个整数，因此会自动把@ManagerIDs转换成变量。

　　为了解决这个问题，你可以运用动态SQL执行这个语句。这样，你就能在执行它之前动态地建立整个查询。

　　Declare @ManagerIDs Varchar(100)
　　Set @ManagerIDs = '3,6'
　　Declare @SQL Varchar(1000)
　　Set @SQL =
　　'Select * from HumanResources.Employee
　　Where ManagerID IN (' + @ManagerIDs + ')'
　　EXEC (@SQL)

　　这样能让你执行这个查询，但是动态SQL是个危险分子，在一些特定的组织中甚至不被允许使用。

　　那么你要如何在不使用动态SQL的情况下执行查询呢?可以通过XML实现。

　　第一步，你需要从一个以逗划定界限的字符串中产生一个XML字段。

　　Declare @ManagerIDs Varchar(100)
　　Set @ManagerIDs = '3,6'
　　DECLARE @XmlStr XML
　　SET @XmlStr =
　　--Start Tag
　　'' +
　　--Replace all commas with an ending tag and start a new tag
　　REPLACE( @ManagerIDs, ',', '') +
　　--End Tag
　　''

　　接着，选择这个XML值，结果显示如下：

　　Select @XmlStr

　　既然你有一个XML字段，我们就可以查询它，结果按行显示如下：

　　SELECT x.ManagerID.value('.', 'INT') AS A
　　FROM @XmlStr.nodes('//ManagerID') x(ManagerID)

　　现在，你可以利用之前的查询来限制结果：

　　SELECT *
　　FROM HumanResources.Employee
　　WHERE ManagerID IN(
　　SELECT x.ManagerID.value('.', 'INT') AS A
　　FROM @XmlStr.nodes('//ManagerID') x(ManagerID)
　　)

　　或者，你可以利用Inner Join来限制结果：

　　SELECT *
　　FROM HumanResources.Employee AS A
　　INNER JOIN
　　(SELECT x.ManagerID.value('.', 'INT') AS ManagerID
　　FROM @XmlStr.nodes('//ManagerID') x(ManagerID)) B
　　ON A.ManagerID = B.ManagerID

（责任编辑：IORI）

阅读次数：

上一篇：VMworld 2008再度掀起x86虚拟化的竞争下一篇：侠诺ARP智能一键双绑增强企业网络防御力

[收藏] [推荐] [评论(0条)] [返回顶部] [打印本页] [关闭窗口]