Dapper Stored Procedures — Calling and Mapping Stored Procedure Results

// Use CommandType.StoredProcedure — Dapper sends EXEC sp_name
var patients = await connection.QueryAsync<PatientDto>(
    "sp_GetWardPatients",
    new { WardId = wardId, IncludeArchived = false },
    commandType: CommandType.StoredProcedure);

// Without CommandType.StoredProcedure, Dapper sends the string as raw SQL.
// CommandType.StoredProcedure generates: EXEC sp_GetWardPatients @WardId = @p0, ...

// With timeout
var result = await connection.QueryFirstOrDefaultAsync<PatientDto>(
    "sp_GetPatientByMrn",
    new { Mrn = mrn },
    commandType:    CommandType.StoredProcedure,
    commandTimeout: 30);

// Stored procedure with INPUT + OUTPUT parameters + RETURN value
// CREATE PROCEDURE sp_AdmitPatient
//   @PatientId    UNIQUEIDENTIFIER,
//   @WardId       UNIQUEIDENTIFIER,
//   @NewAdmissionId UNIQUEIDENTIFIER OUTPUT
// AS ...

var parameters = new DynamicParameters();
parameters.Add("@PatientId",      patientId,     DbType.Guid);
parameters.Add("@WardId",         wardId,        DbType.Guid);
parameters.Add("@NewAdmissionId", dbType: DbType.Guid, direction: ParameterDirection.Output);
parameters.Add("@ReturnValue",    dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

await connection.ExecuteAsync(
    "sp_AdmitPatient",
    parameters,
    commandType: CommandType.StoredProcedure);

var admissionId = parameters.Get<Guid>("@NewAdmissionId");
var returnCode  = parameters.Get<int>("@ReturnValue");
// returnCode: 0 = success, 1 = ward full, 2 = patient already admitted

// Stored procedure that returns multiple result sets
// sp_GetPatientFullRecord returns:
//   Result 1: patient demographics
//   Result 2: active prescriptions
//   Result 3: recent lab results

using var multi = await connection.QueryMultipleAsync(
    "sp_GetPatientFullRecord",
    new { PatientId = patientId },
    commandType: CommandType.StoredProcedure);

var patient       = await multi.ReadFirstOrDefaultAsync<PatientDto>();
var prescriptions = (await multi.ReadAsync<PrescriptionDto>()).AsList();
var labResults    = (await multi.ReadAsync<LabResultDto>()).AsList();

return new PatientFullRecordDto(patient, prescriptions, labResults);

// SQL: CREATE TYPE dbo.ObservationList AS TABLE (
//   PatientId  UNIQUEIDENTIFIER,
//   Type       NVARCHAR(50),
//   Value      DECIMAL(10,4),
//   RecordedAt DATETIME2)

// C#: build DataTable matching the TVP schema
var tvp = new DataTable();
tvp.Columns.Add("PatientId",  typeof(Guid));
tvp.Columns.Add("Type",       typeof(string));
tvp.Columns.Add("Value",      typeof(decimal));
tvp.Columns.Add("RecordedAt", typeof(DateTime));

foreach (var obs in observations)
    tvp.Rows.Add(obs.PatientId, obs.Type, obs.Value, obs.RecordedAt);

var parameters = new DynamicParameters();
parameters.Add("@Observations", tvp.AsTableValuedParameter("dbo.ObservationList"));

await connection.ExecuteAsync(
    "sp_BulkInsertObservations",
    parameters,
    commandType: CommandType.StoredProcedure);

Stored procedures are appropriate when:
  ✓ Complex business logic already exists in SQL and the team owns the database
  ✓ Security: grant EXECUTE on procs, deny direct table SELECT/INSERT access
  ✓ Performance-critical queries where you need the exact execution plan
  ✓ Batch operations with MERGE, temp tables, or set-based logic
  ✓ Legacy systems where the database is the primary deployment artifact

Stored procedures create friction when:
  ✗ The application team does not own the database (DBA bottleneck)
  ✗ Schema evolution requires coordinated application + proc changes
  ✗ Unit testing business logic requires a database (no mocking)
  ✗ Code review for SQL procs is in a separate system from application code

In modern .NET systems: prefer inline parameterized SQL in repositories
  unless the organization has established stored procedure governance.

public interface IWarfarinRepository
{
    Task<WarfarinDoseSuggestionDto?> GetDoseSuggestionAsync(
        Guid patientId, CancellationToken ct);
}

public sealed class WarfarinRepository : IWarfarinRepository
{
    private readonly ISqlConnectionFactory _factory;

    public async Task<WarfarinDoseSuggestionDto?> GetDoseSuggestionAsync(
        Guid patientId, CancellationToken ct)
    {
        using var connection = _factory.CreateConnection();

        var parameters = new DynamicParameters();
        parameters.Add("@PatientId", patientId, DbType.Guid);
        parameters.Add("@SuggestedDose", dbType: DbType.Decimal, direction: ParameterDirection.Output, precision: 10, scale: 4);
        parameters.Add("@Reason",        dbType: DbType.String,  direction: ParameterDirection.Output, size: 500);
        parameters.Add("@ReturnValue",   dbType: DbType.Int32,   direction: ParameterDirection.ReturnValue);

        await connection.ExecuteAsync(
            "sp_CalculateWarfarinDoseSuggestion",
            parameters,
            commandType: CommandType.StoredProcedure);

        if (parameters.Get<int>("@ReturnValue") != 0)
            return null;

        return new WarfarinDoseSuggestionDto(
            parameters.Get<decimal>("@SuggestedDose"),
            parameters.Get<string>("@Reason") ?? string.Empty);
    }
}