Stored Procedures with Dapper: Output, Return Value and the Auto-Detection Trap of 2.1.11

Version note: The code in this post targets Dapper 2.1 and .NET 10.

Guess First

The Bookstore database on SQL Server has a stored procedure called dbo.CountBooks that returns the number of books. Neither of the two calls below specifies a commandType:

using var conn = new SqlConnection(connectionString);

var total = conn.ExecuteScalar<int>("dbo.CountBooks");
Console.WriteLine(total);

conn.Execute("checkpoint");

The first line is a procedure name, the second is SQL Server's familiar CHECKPOINT command. Which one works? Both, neither, or just one?

4
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'checkpoint'.

Giving the procedure name was enough; Dapper called it as a procedure. The plain T-SQL command, on the other hand, failed with "could not find stored procedure". Both outcomes have the same cause: since Dapper 2.1.11, command text without whitespace is treated as a stored procedure. In this post we cover stored procedures from start to finish and give that behavior a section of its own.

Why Are We Leaving SQLite?

Every example in the series so far ran on zero-setup SQLite. This post has to break that, because SQLite has no such thing as a stored procedure. The provider says so plainly:

using var sqlite = new SqliteConnection("Data Source=:memory:");
sqlite.Open();

sqlite.Execute("analyze");

Unhandled exception. System.ArgumentException: The CommandType 'StoredProcedure' is not supported.

We did not even want to call a procedure; because analyze is a single word, Dapper took it for one and Microsoft.Data.Sqlite rejected that command type. So automatic detection can find you on SQLite too. That is enough for now; let's get to the real topic.

We need a real SQL Server, and docker is the easiest way. On an Apple Silicon Mac the image runs under emulation; it is a bit slow but does the job:

docker run -d --name bookstore-sql -e ACCEPT_EULA=Y -e "MSSQL_SA_PASSWORD=Bookstore_Pass1" -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest
docker run -d --name bookstore-pg -e POSTGRES_PASSWORD=bookstore -e POSTGRES_DB=bookstore -p 5432:5432 postgres:17-alpine
dotnet add package Microsoft.Data.SqlClient --version 7.1.1
dotnet add package Npgsql --version 10.0.3

We use Microsoft.Data.SqlClient as the provider. The old System.Data.SqlClient package is now marked "deprecated" on NuGet; there is no reason to go back to it in a new project. I ran the examples on SQL Server 2022 (CU24) and PostgreSQL 17.

The First Stored Procedure Call

The SQL Server version of the Bookstore schema and our first procedure:

create table dbo.authors (id int identity primary key, name nvarchar(100) not null);
create table dbo.books (id int identity primary key, title nvarchar(200) not null,
    author_id int not null references dbo.authors(id), price decimal(10,2) not null);
insert dbo.authors (name) values (N'Frank Herbert'), (N'Ursula K. Le Guin');
insert dbo.books (title, author_id, price) values
    (N'Dune', 1, 12.50), (N'Dune Messiah', 1, 10.00),
    (N'The Left Hand of Darkness', 2, 11.00), (N'The Dispossessed', 2, 13.00);

create or alter procedure dbo.GetBooksByAuthor
    @AuthorId int
as
begin
    set nocount on;
    select id, title, price
    from dbo.books
    where author_id = @AuthorId
    order by id;
end

On the Dapper side, the only thing that changes is commandType. We pass the procedure name instead of SQL and still pass parameters with an anonymous object:

var connectionString = "Server=localhost,1433;Database=Bookstore;User Id=sa;Password=Bookstore_Pass1;TrustServerCertificate=True";
using var conn = new SqlConnection(connectionString);

var books = conn.Query<BookRow>(
    "dbo.GetBooksByAuthor",
    new { AuthorId = 2 },
    commandType: CommandType.StoredProcedure);

foreach (var b in books)
    Console.WriteLine($"{b.Id} | {b.Title} | {b.Price}");

public record BookRow(int Id, string Title, decimal Price);

3 | The Left Hand of Darkness | 11.00
4 | The Dispossessed | 13.00

The property is named AuthorId, the procedure parameter @AuthorId. You don't need the leading @; SqlClient matches parameters by name in a procedure call. That is why order does not matter, but getting the name exactly right does. Send an extra property and the procedure fails with "too many arguments".

Output Parameters

A procedure that inserts a new book and returns the generated id through an output parameter:

create or alter procedure dbo.AddBook
    @Title nvarchar(200),
    @AuthorId int,
    @Price decimal(10, 2),
    @BookId int output
as
begin
    set nocount on;
    insert dbo.books (title, author_id, price) values (@Title, @AuthorId, @Price);
    set @BookId = cast(scope_identity() as int);
end

An anonymous object cannot say which direction a parameter goes. This is where DynamicParameters comes in: we mark the parameter as output with direction and read the value after the call with Get<T>:

var p = new DynamicParameters();
p.Add("Title", "Children of Dune");
p.Add("AuthorId", 1);
p.Add("Price", 11.75m);
p.Add("BookId", dbType: DbType.Int32, direction: ParameterDirection.Output);

conn.Execute("dbo.AddBook", p, commandType: CommandType.StoredProcedure);

Console.WriteLine(p.Get<int>("BookId"));

5

Think of an output parameter as an envelope: you hand the procedure an empty envelope, it puts something inside and sends it back. You also have to say up front how big the envelope is. For numbers the type tells you; for strings it does not:

create or alter procedure dbo.GetAuthorName
    @AuthorId int,
    @Name nvarchar(100) output
as
begin
    set nocount on;
    select @Name = name from dbo.authors where id = @AuthorId;
end

var p = new DynamicParameters(new { AuthorId = 2 });
p.Add("Name", dbType: DbType.String, direction: ParameterDirection.Output);

conn.Execute("dbo.GetAuthorName", p, commandType: CommandType.StoredProcedure);
Console.WriteLine(p.Get<string>("Name"));

Unhandled exception. System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.

The error comes from SqlClient: because we gave no length for the output string, SqlClient refuses to send the parameter. Add size: 100, the same value as the column's nvarchar(100) definition, and the output becomes Ursula K. Le Guin. Note the DynamicParameters(new { AuthorId = 2 }) form as well: passing the inputs as an anonymous object and adding only the output with Add keeps the code short.

Return Value

Every stored procedure can return an integer. It is usually used as a "status code": success, not found, business rule violation and so on:

create or alter procedure dbo.DiscountBooks
    @AuthorId int,
    @Percent int
as
begin
    set nocount on;
    if not exists (select 1 from dbo.authors where id = @AuthorId)
        return -1;

    update dbo.books
    set price = price * (100 - @Percent) / 100.0
    where author_id = @AuthorId;

    return @@rowcount;
end

To read the return value we add a parameter with ParameterDirection.ReturnValue. Its name is entirely arbitrary, because it has no counterpart in the procedure:

foreach (var authorId in new[] { 1, 99 })
{
    var p = new DynamicParameters(new { AuthorId = authorId, Percent = 10 });
    p.Add("Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

    var affected = conn.Execute("dbo.DiscountBooks", p, commandType: CommandType.StoredProcedure);
    Console.WriteLine($"author {authorId}: return = {p.Get<int>("Result")}, Execute = {affected}");
}

author 1: return = 3, Execute = -1
author 99: return = -1, Execute = -1

Frank Herbert's three books got the discount, and for the missing author the procedure returned -1. But look at what Execute itself returned: -1 both times. The server does not report the affected row count, because the procedure's first line is set nocount on. We will come back to this in the pitfalls section.

A Result Set and an Output Together

A common pattern for paged lists: the procedure returns one page of rows and also writes the total count into an output.

create or alter procedure dbo.GetBookPage
    @Skip int,
    @Take int,
    @Total int output
as
begin
    set nocount on;
    select @Total = count(*) from dbo.books;

    select id, title, price
    from dbo.books
    order by id
    offset @Skip rows fetch next @Take rows only;
end

var p = new DynamicParameters(new { Skip = 0, Take = 2 });
p.Add("Total", dbType: DbType.Int32, direction: ParameterDirection.Output);

var page = conn.Query<BookRow>("dbo.GetBookPage", p, commandType: CommandType.StoredProcedure);
Console.WriteLine($"{page.Count()} of {p.Get<int>("Total")}");

2 of 5

That worked fine with a buffered query. The key fact is this: in the TDS protocol, output values arrive after the result sets. A buffered Query reads every row and closes the reader, so the value is ready when Get is called. Switch to the unbuffered reading from post 10 and the picture changes:

var stream = conn.Query<BookRow>("dbo.GetBookPage", p,
    commandType: CommandType.StoredProcedure, buffered: false);

foreach (var b in stream)
{
    Console.WriteLine(b.Title);
    Run(() => Console.WriteLine(p.Get<int>("Total")));
}
Console.WriteLine(p.Get<int>("Total"));

static void Run(Action action)
{
    try { action(); }
    catch (Exception ex) { Console.WriteLine(ex.GetType() + ": " + ex.Message); }
}

Dune
System.NullReferenceException: Object reference not set to an instance of an object.
Dune Messiah
System.NullReferenceException: Object reference not set to an instance of an object.
5

Inside the loop the value is not there yet; after the loop it is. Dapper's source even has an explanatory message for this case ("out/return parameters will not have updated values until the data stream completes"), but it is thrown only when the value is DBNull. SqlClient leaves the output as null while reading is in progress, so you get a bare NullReferenceException. The same rule applies to QueryMultiple: ask for output values after all grids have been read.

The Surprise of 2.1.11: Automatic Detection

Back to the opening guess. When no commandType is given, Dapper looks at the command text and picks the type itself. The rule in the 2.1.89 source, in short: if the text contains a whitespace character (Unicode whitespace included) or ;, /, -, +, *, or if it is exactly vacuum, commit, rollback or revert (case-insensitive), the command goes as text. Everything else is treated as a stored procedure.

That is why "dbo.CountBooks" and "[dbo].[CountBooks]" were called as procedures, and so was "checkpoint". The exception list grew in 2.1.15 and 2.1.21, and REVERT was added in 2.1.35; in other words, the list is made of commands people actually tripped over. If the single-word command you use is not on the list, the fix is simple: state the type explicitly:

conn.Execute("checkpoint", commandType: CommandType.Text);

My rule: when calling a procedure, always write commandType: CommandType.StoredProcedure explicitly. Detection is a convenience, but that argument is the one place that tells a reader "this is a procedure". If you build command text dynamically, for example running maintenance commands from an admin screen, write CommandType.Text explicitly too.

PostgreSQL: A Function Is Not a Procedure

Here is the first surprise for someone moving from SQL Server to PostgreSQL. In PostgreSQL, routines that return data are usually functions; procedures arrived in version 11, are called with CALL and do not return result sets. Let's define one function and one procedure in the PostgreSQL version of Bookstore (Npgsql 10.0.3):

create function get_books_by_author(p_author_id int)
returns table (id int, title text, price numeric)
language sql
as $$
    select b.id, b.title, b.price from books b where b.author_id = p_author_id order by b.id;
$$;

create procedure add_book(p_title text, p_author_id int, p_price numeric, inout p_book_id int default null)
language plpgsql
as $$
begin
    insert into books (title, author_id, price) values (p_title, p_author_id, p_price)
    returning id into p_book_id;
end;
$$;

If we call the function with CommandType.StoredProcedure out of SQL Server habit:

var pgConnectionString = "Host=localhost;Port=5432;Username=postgres;Password=bookstore;Database=bookstore";
using var pg = new NpgsqlConnection(pgConnectionString);

var books = pg.Query<BookRow>(
    "get_books_by_author",
    new { p_author_id = 2 },
    commandType: CommandType.StoredProcedure);

Unhandled exception. Npgsql.PostgresException (0x80004005): 42883: procedure get_books_by_author(p_author_id => integer) does not exist

Since Npgsql 7, CommandType.StoredProcedure produces a CALL, so it only works for real procedures. You can bring back the old behavior with AppContext.SetSwitch("Npgsql.EnableStoredProcedureCompatMode", true); I tried it and it works. But the natural way is to query the function like a table:

var books = pg.Query<BookRow>(
    "select * from get_books_by_author(@AuthorId)",
    new { AuthorId = 2 });

foreach (var b in books)
    Console.WriteLine($"{b.Id} | {b.Title} | {b.Price}");

3 | The Left Hand of Darkness | 11.00
4 | The Dispossessed | 13.00

The procedure's inout parameter maps to the InputOutput direction on the Dapper side:

var p = new DynamicParameters();
p.Add("p_title", "Children of Dune");
p.Add("p_author_id", 1);
p.Add("p_price", 11.75m);
p.Add("p_book_id", dbType: DbType.Int32, direction: ParameterDirection.InputOutput);

pg.Execute("add_book", p, commandType: CommandType.StoredProcedure);
Console.WriteLine(p.Get<int>("p_book_id"));

5

Common Pitfalls

Trusting what Execute returns. In a procedure with set nocount on, the affected row count never reaches the client and Execute returns -1. I tried it with two procedures that run the same update:

Console.WriteLine(conn.Execute("dbo.RenameAuthor",
    new { AuthorId = 1, Name = "Frank Herbert" }, commandType: CommandType.StoredProcedure));
Console.WriteLine(conn.Execute("dbo.RenameAuthorQuiet",
    new { AuthorId = 1, Name = "Frank Herbert" }, commandType: CommandType.StoredProcedure));

1
-1

The only difference in the second one is set nocount on. If the affected row count matters for a business rule, return it explicitly through a return value or an output parameter; turning nocount off means needless network traffic.

Giving output strings no size, or the wrong one. A zero size throws; a size shorter than the column silently truncates the value. Keep the size identical to the procedure's definition.

Reading the output too early. Call Get in an unbuffered query or with QueryMultiple before all rows are read and SqlClient gives you a NullReferenceException. Ask once reading is done.

Sending extra properties. Procedures do not ignore unused parameters the way SQL text does. Pass a DTO straight in as the param, and every property without a counterpart in the procedure turns into a "too many arguments" error. Either build a purpose-made anonymous object or fill DynamicParameters by hand.

A note for comparing with EF Core: in EF Core you call a procedure with FromSql or ExecuteSql, and for an output parameter you have to drop down to the provider's own SqlParameter. In Dapper the same job stays provider-independent with DynamicParameters. Post 15 of the EF Core series covers that side.

A Small Experiment

★☆☆ Add an optional parameter @MaxPrice decimal(10,2) = null to dbo.GetBooksByAuthor. Make two calls from Dapper, one without sending this parameter at all and one sending null, and think about why the results are the same.

★★☆ Change dbo.AddBook so it returns return 1 if the book title already exists and return 0 if it inserted. Read both the output and the return value in the same call and print both cases.

★★★ Create a CommandDefinition without a commandType for the texts "sp_who", "VACUUM", "select 1" and "dbo.CountBooks--", and print its CommandType property. Guess which one falls into which type before you reread the rule.

While we are on SQL Server, let's stay: in the next post, again on SQL Server in docker, we look at table-valued parameters, how ANSI strings affect indexes, {=literal} replacement, and what parameter sniffing and IN lists do to the plan cache.