Dapper调用存储过程：如何通过OUTPUT参数获取插入行ID？

编写了如下SQL存储过程，意图通过INSERT的OUTPUT子句返回插入的AccountId到输出参数：

create or alter procedure dbo.spAddAccount
    @AccountName varchar(100),
    @OpeningBalance money,
    @AccountTypeId tinyint,
    @AccountId tinyint output
as
begin
        
    insert into dbo.Accounts (AccountName, OpeningBalance, AccountTypeId)
    output inserted.AccountId
    values (@AccountName, @OpeningBalance, @AccountTypeId);
end

通过C# Dapper调用的代码如下：

var parameters = new DynamicParameters();
parameters.Add("AccountName", dbType: DbType.String, direction: ParameterDirection.Input, value: account.AccountName);
parameters.Add("OpeningBalance", dbType: DbType.String, direction: ParameterDirection.Input, value: account.OpeningBalance);
parameters.Add("AccountTypeId", dbType: DbType.Byte, direction: ParameterDirection.Input, value:account.AccountTypeId);
parameters.Add("AccountId", dbType: DbType.Byte, direction: ParameterDirection.Output);
        
await using var sqlConnection = new SqlConnection(ConnectionString);
await sqlConnection.ExecuteAsync(
    "spAddAccount",
    param: parameters,
    commandType: CommandType.StoredProcedure);

return parameters.Get<byte>("@AccountId");

但无论通过Dapper调用还是直接在SQL Shell执行：

declare @accountId tinyint;

exec spAddAccount 'Foo', 0, 1, @accountId output
select @accountId;

输出参数@AccountId始终为null。尝试过output inserted.AccountId as '@AccountId'也无效，希望通过OUTPUT子句而非SCOPE_IDENTITY()解决问题。

1. 修正存储过程的输出参数赋值逻辑 #

原存储过程中OUTPUT inserted.AccountId只是将结果返回为结果集，并未赋值给输出参数@AccountId。需要将OUTPUT的结果捕获后赋值给输出参数：

create or alter procedure dbo.spAddAccount
    @AccountName varchar(100),
    @OpeningBalance money,
    @AccountTypeId tinyint,
    @AccountId tinyint output
as
begin
    -- 用变量捕获插入的ID
    declare @InsertedId tinyint;

    insert into dbo.Accounts (AccountName, OpeningBalance, AccountTypeId)
    output inserted.AccountId into @InsertedId
    values (@AccountName, @OpeningBalance, @AccountTypeId);

    -- 将捕获的值赋值给输出参数
    set @AccountId = @InsertedId;
end

或者更简洁的写法（直接将输出结果存入输出参数）：

create or alter procedure dbo.spAddAccount
    @AccountName varchar(100),
    @OpeningBalance money,
    @AccountTypeId tinyint,
    @AccountId tinyint output
as
begin
    insert into dbo.Accounts (AccountName, OpeningBalance, AccountTypeId)
    output inserted.AccountId into @AccountId
    values (@AccountName, @OpeningBalance, @AccountTypeId);
end

2. 修正C#代码中的参数类型错误 #

@OpeningBalance在存储过程中是money类型，但C#代码中错误设置为DbType.String，需改为DbType.Currency：

parameters.Add("OpeningBalance", dbType: DbType.Currency, direction: ParameterDirection.Input, value: account.OpeningBalance);

3. 修正输出参数的获取方式 #

获取输出参数时不需要加@前缀，直接使用参数名：

return parameters.Get<byte>("AccountId");

完成以上修改后，即可正确获取插入的AccountId。

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