Dapper, stored procedures and returning inserted row id via OUTPUT parameter-CodePudding

I have the following simple stored procedure:

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

I'm calling it via Dapper from C# using the following code:

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");

This doesn't work, as @AccountId is always null.

If I run the stored procedure via a SQL shell like so:

declare @accountId tinyint;

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

Then again, @accountId is always null.

I think it's because I need to assign the result of output inserted.AccountId to @accountId, as it's not smart enough to assign it to my declared output parameter. But I can't find how to do this.

I know I could use scope_identity(), but I was wondering how to get it working using output in the insert statement.

I also tried

output inserted.AccountId as '@AccountId`

but that's also no good.

Thanks

CodePudding user response：

This works for the body of the stored proc

DECLARE @insertResult table (accountId tinyint)

insert into dbo.Accounts            
(AccountName, OpeningBalance, AccountTypeId)
OUTPUT INSERTED.AccountId into @insertResult
values
(@AccountName, @OpeningBalance, @AccountTypeId);

set @AccountId = (select accountId from @insertResult);