动态SQL中如何将指定列赋值给变量？

原代码 #

DECLARE @DATE    nvarchar(4) = '0824';
DECLARE @LocalID INT;

SET @sql = N'SELECT Col1, Col2, Col3, Col4, ';

SET @sql = @sql + N'@LocalID = Col5 FROM Table WHERE AdminDate = @InternalDate ';

EXEC sp_executesql @sql, N'@InternalDate nvarchar(4), @InternalLocalID INT OUTPUT', 
     @InternalDate = @Date, @InternalLocalID = @LocalID OUTPUT;

SELECT @LocalID AS LocalID;

错误提示 #

为变量赋值的SELECT语句不能与数据检索操作结合使用
（原英文：A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations）

问题分析 #

你的判断没错，错误根源就是同一个SELECT语句里同时执行了两种操作：返回Col1-Col4的结果集，又给@LocalID变量赋值。SQL Server不允许这种混合操作，必须将两者分开处理。

解决方法 #

方法1：拆分两次查询

分别执行数据检索和变量赋值，逻辑清晰直观：

DECLARE @DATE    nvarchar(4) = '0824';
DECLARE @LocalID INT;

-- 第一步：返回需要的列数据
SET @sql = N'SELECT Col1, Col2, Col3, Col4 FROM Table WHERE AdminDate = @InternalDate';
EXEC sp_executesql @sql, N'@InternalDate nvarchar(4)', @InternalDate = @DATE;

-- 第二步：单独给变量赋值
SET @sql = N'SELECT @InternalLocalID = Col5 FROM Table WHERE AdminDate = @InternalDate';
EXEC sp_executesql @sql, N'@InternalDate nvarchar(4), @InternalLocalID INT OUTPUT', 
     @InternalDate = @DATE, @InternalLocalID = @LocalID OUTPUT;

SELECT @LocalID AS LocalID;

方法2：用表变量整合操作

如果希望在一次sp_executesql调用内完成所有操作，可以用表变量存储检索结果，同时完成变量赋值：

DECLARE @DATE    nvarchar(4) = '0824';
DECLARE @LocalID INT;
-- 根据实际列类型定义表变量
DECLARE @TempTable TABLE (Col1 INT, Col2 INT, Col3 INT, Col4 INT);

SET @sql = N'
-- 把需要返回的列插入表变量
INSERT INTO @TempTable (Col1, Col2, Col3, Col4)
SELECT Col1, Col2, Col3, Col4 FROM Table WHERE AdminDate = @InternalDate;
-- 单独赋值变量
SELECT @InternalLocalID = Col5 FROM Table WHERE AdminDate = @InternalDate;
';

EXEC sp_executesql @sql, N'@InternalDate nvarchar(4), @InternalLocalID INT OUTPUT', 
     @InternalDate = @DATE, @InternalLocalID = @LocalID OUTPUT;

-- 查看检索到的列数据
SELECT * FROM @TempTable;
-- 查看赋值后的变量
SELECT @LocalID AS LocalID;

方法3：仅保留变量赋值（无需返回其他列）

如果你的需求只是把Col5赋值给变量，不需要返回Col1-Col4，直接简化动态SQL即可：

DECLARE @DATE    nvarchar(4) = '0824';
DECLARE @LocalID INT;

SET @sql = N'SELECT @InternalLocalID = Col5 FROM Table WHERE AdminDate = @InternalDate';

EXEC sp_executesql @sql, N'@InternalDate nvarchar(4), @InternalLocalID INT OUTPUT', 
     @InternalDate = @DATE, @InternalLocalID = @LocalID OUTPUT;

SELECT @LocalID AS LocalID;

内容的提问来源于stack exchange，提问作者Stephen