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 @sqlgo 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)goDECLARE @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