SQL Server Dynamic SQL

1. An Exec Example

DECLARE @schemaname AS NVARCHAR(128), @tablename AS NVARCHAR(128), @objectname AS NVARCHAR(517);
SET @schemaname = N'dbo';
SET @tablename = N'Orders';
SET @objectname = QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename);
EXEC(N'SELECT COUNT(*) FROM ' + @objectname + N';');

2. Exec At
通过DB Link执行一个动态SQL

EXEC
(
'SELECT ProductID, ProductName, UnitPrice
FROM Northwind.dbo.Products
WHERE ProductID = ?;', 3
) AT [Dojo];

3.sp_executesql
相对EXEC而言,sp_executesql可以增加执行计划的稳定性,减少重复解析.

语法:
EXEC sp_executesql
@stmt = , -- similar to proc's body
@params = , -- similar to proc's params declaration
-- like in a procedure call

例子:
DECLARE @i AS INT;
SET @i = 10248;

DECLARE @sql AS NVARCHAR(46);
SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = @oid;';

EXEC sp_executesql
@stmt = @sql,
@params = N'@oid AS INT',
@oid = @i;

sp_executesql不仅允许input参数,也允许output参数

DECLARE @sql AS NVARCHAR(12)
DECLARE @i AS INT

SET @sql = N'SET @p = 10;';

EXEC sp_executesql
@stmt = @sql,
@params = N'@p AS INT OUTPUT',
@p = @i OUTPUT;

SELECT @i;

[@more@]