动态SQL语句

摘要：有些时候我们需要执行的SQL不是固定不变的，而是需要动态执行的，此时你就需要使用到动态SQL语句，今天我们就一块看一下常见的动态SQL使用方法。

主要内容

1. 动态执行SQL的两种方式
2. 动态SQL输出参数
3. 执行动态SQL的注意事项

一、动态SQL两种执行方式

EXEC方式

我们知道在执行存储过程时经常用到"EXEC"命令，例如要想执行存储过程"CustOrderHist"就可以使用"EXEC CustOrderHist 'ALFKI'"。其实"EXEC"还有另一种用法就是"EXEC (sql)"，通过这种方式我们就可以动态执行sql语句。例如现在我想写一个存储过程，参数就表名和所选择的列，我通过传入这两个参数可以得到指定表的指定列信息，就可以这样来写：

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Kenshin Cui -- Create date: 2010.11.20 -- Description: Based on the specified table and column returns the query data -- ============================================= CREATE PROCEDURE GetQueryDataByTableNameAndColumns @tableName NVARCHAR(20), @columns NVARCHAR(200) AS BEGIN DECLARE @sql NVARCHAR(500) SET @sql='SELECT '+@columns+' FROM '+@tableName EXEC (@sql) END GO

有了上面的存储过程，例如执行" EXEC dbo . GetQueryDataByTableNameAndColumns @tableName = 'Products' , @columns = 'ProductName,UnitPrice' "就可以看到：

在这个存储过程中我们就通过使用"EXEC"来动态执行了SQL。

sp_executesql方式

"sp_executesql"本身是一个存储过程（系统提供），在SQL SERVER 2005及其以上版本才有。通过它我们也可以执行动态SQL，而且通常情况下我们推荐使用这种方法。现在我们就一块看看它的使用方法。假设现在还是实现如上功能我们就可以这么做：

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Kenshin Cui -- Create date: 2010.11.20 -- Description: Based on the specified table and column returns the query data -- ============================================= CREATE PROCEDURE GetQueryDataByTableNameAndColumns2 @tableName NVARCHAR(20), @columns NVARCHAR(200) AS BEGIN DECLARE @sql NVARCHAR(500) SET @sql='SELECT '+@columns+' FROM '+@tableName EXEC sys.sp_executesql @sql END GO

执行结果同上面的"EXEC"方式完全相同。

二、带有输出参数的动态SQL

虽然上面两种方式都能够动态执行SQL，但是有些时候你的动态语句中可能还需要带有参数，这时你可能希望在动态执行SQL的同时能够将你的参数返回。例如现在我想要写一个存储过程，功能就是通过传入表名和列名，返回指定列中最大的列值。当然，这是你需要定义一个参数来接收"MAX"值，可是关键问题就是怎么样返回这个值呢？答案就是使用"sp_executesql"方式。"sp_executesql"支持输出参数，这个存储过程除了可以传入一个参数之外（例如我们的例子），还支持传入三个参数（这说明这个存储过程是有两个可选参数的），后者也正可以解决我们的问题。例如要实现之前说的功能就可以这样下：

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Kenshin Cui -- Create date: 2010.11.20 -- Description: By specifying the table name and column names to return the maximum value -- ============================================= CREATE PROCEDURE GetMaxValueByTableNameAndColumns @tableName NVARCHAR(20), @columnName NVARCHAR(200) AS BEGIN DECLARE @sql NVARCHAR(500) DECLARE @outputParamDefine NVARCHAR(100) DECLARE @maxValue NVARCHAR(50) SET @sql='SELECT @maxValue=MAX('+@columnName+') FROM '+@tableName SET @outputParamDefine='@maxValue NVARCHAR(50) OUTPUT' EXEC sys.sp_executesql @sql,@outputParamDefine,@maxValue OUTPUT SELECT @maxValue END GO

执行"EXEC dbo . GetMaxValueByTableNameAndColumns @tableName = 'Products' , @columnName = 'ProductID' "后：

三、注意事项

1．使用"EXEC"实现动态SQL的时候切记在语句前后加上括号。

2.使用"sp_executesql"实现动态SQL时一定注意语句本身和其参数都必须是"NTEXT"、"NCHAR"、"NVARCHAR"型，而不能是"VARCHAR"等非N型（类型前面没有字母"N"的类型），并且如果是语句的话语句前需要加字母"N"（注意是语句而非变量，变量的话就不需要了；通过在语句前添加"N"的目的也是将其转化为"NTEXT/ NCHAR / NVARCHAR"型）；"EXEC"方式可以直接不通过其他变量执行"SQL拼接"（注意是"SQL拼接"而不是"SQL语句"，对于不经过拼接的SQL前加上"N"是可以使用"sp_executesql"执行的），而"sp_executesql"不能（即使前面加上"N"）。

下面列出了上面说的几种情况：

DECLARE @tableName NVARCHAR(20) DECLARE @columns NVARCHAR(200) DECLARE @sql NVARCHAR(500)--当使用sp_executesql执行时不能是VARCHAR(500) SET @tableName='Products' SET @columns='ProductName,UnitPrice' SET @sql=N'SELECT '+@columns+' FROM '+@tableName EXEC (@sql) --正确！！！ EXEC ('SELECT '+@columns+' FROM '+@tableName) --正确！！！ EXEC sys.sp_executesql @sql --正确！！！但是如果@sql定义成varchar类型此句就会出错 EXEC sys.sp_executesql N'SELECT ProductName,UnitPrice FROM Products'--正确！！！直接执行语句，语句前面必须加“N” EXEC sys.sp_executesql N'SELECT '+@columns+' FROM '+@tableName --错误！！！不能执行sql拼接，即使前面加N