sp_executesql和exec使用注意事项

      一.使用注意

       当 sp_executesql 或 EXECUTE 语句执行字符串时，字符串将作为它的自包含批处理执行。SQL Server 会将字符串中的一个或多个 Transact-SQL 语句编译为独立于批处理（包含 sp_executesql 或 EXECUTE 语句）执行计划的执行计划。下列规则适用于自包含批处理：

• 在执行 sp_executesql 或 EXECUTE 语句之前，不会将 sp_executesql 或 EXECUTE 字符串中的 Transact-SQL 语句编译到执行计划中。执行字符串之前，不会分析或检查其错误。执行时才对字符串中引用的名称进行解析。 

DECLARE @CharVariable CHAR(3);
 SET @CharVariable = 'abc';
 --print还是可以执行输出
 print @CharVariable
 EXECUTE  ('there is a error')
 GO

• 已执行的字符串中的 Transact-SQL 语句不能访问包含 sp_executesql 或 EXECUTE 语句的批处理中声明的任何变量。包含 sp_executesql 或 EXECUTE 语句的批处理不能访问已执行字符串中定义的变量或局部游标。

/*Show not having access to variables from the calling batch. */
 DECLARE @CharVariable CHAR(3);
 SET @CharVariable = 'abc';
 /* sp_executesql fails because @CharVariable has gone out of scope. */
EXECUTE sp_executesql N'PRINT @CharVariable';
GO

• 如果已执行字符串包含一个更改数据库上下文的 USE 语句，则对数据库上下文所做的更改将仅持续到 sp_executesql 或 EXECUTE 语句运行结束。

/* Show database context resetting after sp_executesql finishes. */
USE master;
GO
EXECUTE sp_executesql N'USE AdventureWorks2008R2;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM Sales.Store;
GO

      

       二.sp_executesql和exec的区别

      sp_executesql 支持替换 Transact-SQL 字符串中指定的任何参数值，但 EXECUTE 语句不支持。因此，由 sp_executesql 生成的 Transact-SQL 字符串比那些由 EXECUTE 语句生成的字符串更加相似。SQL Server 查询优化器可能将 sp_executesql 的 Transact-SQL 语句与以前所执行的语句的执行计划相匹配，从而节省编译新的执行计划的开销。

      EXECUTE 语句：所有参数值都必须转换为字符或 Unicode，并成为 Transact-SQL 字符串的一部分。如果重复执行语句，则即使只有提供的参数值不同，每次执行时也必须生成全新的 Transact-SQL 字符串。这样就会以下列方式生成额外的开销：

• 不断更改字符串文本（特别是复杂 Transact-SQL 语句）中的参数值，会影响 SQL Server 查询优化器将新的 Transact-SQL 字符串与现有执行计划相匹配的功能。

• 每次执行时均必须重新生成整个字符串。

• 每次执行时必须将参数值（非字符或 Unicode 值）转换为字符或 Unicode 格式。

      sp_executesql语句：

      1.因为 Transact-SQL 语句的实际文本在两次执行之间未更改，所以查询优化器能够将第二次执行中的 Transact-SQL 语句与第一次执行时生成的执行计划相匹配。因此，SQL Server 不必编译第二条语句。

      2.Transact-SQL 字符串只生成一次。

      3.整型参数按其本身格式指定。不需要转换为 Unicode。

      4.sp_executesql还可以使用output参数

        sp_executesql：可以使用参数,如果直接使用Exec(@sqlStr2)则会提示没有声明标量@age,就算是在@sqlStr2之前就声明@age也是会出错的。

      

DECLARE @sqlStr2 NVARCHAR(500)
SET @sqlStr2=N'select * from person where age=@age'
DECLARE @paratype NVARCHAR(50)
DECLARE @ageInt NVARCHAR(20)
SET @paratype=N'@age int'
SET @ageInt=11
EXEC sys.sp_executesql @sqlStr2,@paratype,@age=@ageInt

       sp_executesql：可以使用OutPut参数 

DECLARE @sqlStr4 NVARCHAR(500)
SET @sqlStr4 = N'select @num=count(*) from person where age=@age'
DECLARE @paratype NVARCHAR(50)
DECLARE @num INT
DECLARE @age INT
SET @paratype = N'@num int output,@age int'
SET @age = 100
EXECUTE sys.sp_executesql @sqlStr4, @paratype, @num = @num OUTPUT, @age = @age
PRINT @num

      但是如果是以表名和字段名称作为sp_executesql的参数变量，也是会出现要先声明标量的错误

--提示为生命标量@tablename
DECLARE @sql NVARCHAR(500)
SET @sql = N'select @fieldname from @tablename'
DECLARE @fielaname NVARCHAR(50)
DECLARE @tablename NVARCHAR(50)
DECLARE @paratype NVARCHAR(100)
SET @paratype = N'@fieldname nvarchar(50),@tablename nvarchar(50)'
SET @fielaname = 'name'
SET @tablename = 'person'
EXECUTE sys.sp_executesql @sql, @paratype, @fielaname, @tablename

--查询的结果都是age
DECLARE @sql NVARCHAR(500)
SET @sql = N'select @fieldname from person'
DECLARE @fielaname NVARCHAR(50)
DECLARE @tablename NVARCHAR(50)
DECLARE @paratype NVARCHAR(100)
SET @paratype = N'@fieldname nvarchar(50)'
SET @fielaname = 'age'
SET @tablename = 'person'
PRINT @sql
EXECUTE sys.sp_executesql @sql, @paratype, @fielaname