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