TSQL中如何将EXECUTE语句的返回值赋值给本地变量

问题根因 #

你当前使用的 exec @id = 存储过程 写法，接收的是存储过程的执行状态返回值，存储过程执行无报错的情况下默认返回0，这就是你得到的@id始终为0的原因。你截图中展示的正确ID是存储过程内部执行SELECT语句输出的结果集，和存储过程的状态返回值是完全不同的两类输出。

解决方案 #

方案1：修改存储过程使用OUTPUT输出参数（推荐） #

如果可以修改AP_GET_NEXTID的定义，优先使用输出参数传递返回值，这是TSQL存储过程返回业务数值的规范写法：

1. 修改存储过程定义，增加OUTPUT类型参数：

ALTER PROCEDURE AP_GET_NEXTID
    @type VARCHAR(20),
    @increment INT,
    @resultId INT OUTPUT -- 新增输出参数
AS
BEGIN
    -- 原有计算ID的逻辑保持不变，把最终算出来的ID赋值给@resultId即可
    -- 示例：SELECT @resultId = max(id) + @increment FROM 你的序号表 WHERE type = @type
    -- 删掉原有直接SELECT @id输出结果集的语句
END

2. 调整调用写法：

declare @id int = -1 
exec AP_GET_NEXTID @type = 'CT', @increment = 1, @resultId = @id OUTPUT
print @id
select @id

方案2：无法修改存储过程时用表变量接收结果集 #

如果没有权限修改存储过程，可以先把存储过程输出的结果集写入表变量，再读取数值：

declare @id int = -1 
-- 定义表变量存储存储过程返回的结果集
declare @tempResult table (resId int)
-- 把执行结果插入表变量
insert into @tempResult(resId)
exec AP_GET_NEXTID @type = 'CT', @increment = 1
-- 从表变量中读取ID赋值
select @id = resId from @tempResult
print @id
select @id

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