sql中EXEC和sp_execsql区别待叙写

DECLARE @TableName VARCHAR(50),@sql NVARCHAR(MAX),@OrderID VARCHAR(50) ,@sql2 NVARCHAR(MAX);
SET @TableName = 'Orders ';
SET @OrderID = N'10251';
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ' WHERE OrderID = '+CAST(@OrderID AS VARCHAR(50)) + ' ORDER BY ORDERID DESC'
EXEC sp_executesql @sql
go

DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT;SET @TableName = 'Orders';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = '+CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'
EXEC(@sql);
--注：这里的EXEC括号中只允许包含一个字符串变量，但是可以串联多个变量，如果我们这样写EXEC：

--EXEC('SELECT TOP('+ CAST(@TopCount AS VARCHAR(10)) +')* FROM '+QUOTENAME(@TableName) +' ORDER BY ORDERID DESC');
--SQL编译器就会报错，编译不通过，而如果我们这样：
EXEC(@sql+@sql2+@sql3)
go

DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT;SET @TableName = 'Orders';
SET @OrderID = 10251;
SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = @OrderID ORDER BY ORDERID DESC'
EXEC(@sql);--必须在串联的语句中生命变量@OrderId使用EXEC时，如果您想访问变量，必须把变量内容串联到动态构建的代码字符串中，如：SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE OrderID = '+CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC'

--串联变量的内容也存在性能方面的弊端。SQL Server为每一个的查询字符串创建新的执行计划，即使查询模式相同也是这样。为演示这一点，先清空缓存中的执行计划

--DBCC FREEPROCCACHE (这个不是本文所涉及的内容，您可以查看MS的MSDN