Output Parameters - lobodava/artisan-orm GitHub Wiki

SQL Server stored procedures can return scalar values through OUTPUT parameters in addition to (or instead of) result sets. Artisan.Orm exposes a typed Add*OutputParam helper for each common SQL type, so you don't have to set up SqlParameter objects by hand.

Added in v4 — see What's New in v4.

The full list

Method	SQL type	.NET type read back as
`AddBitOutputParam(name)`	`bit`	`bool`
`AddTinyIntOutputParam(name)`	`tinyint`	`byte`
`AddSmallIntOutputParam(name)`	`smallint`	`short`
`AddIntOutputParam(name)`	`int`	`int`
`AddBigIntOutputParam(name)`	`bigint`	`long`
`AddDecimalOutputParam(name, precision, scale)`	`decimal(p, s)`	`decimal`
`AddGuidOutputParam(name)`	`uniqueidentifier`	`Guid`
`AddDateTime2OutputParam(name)`	`datetime2(7)`	`DateTime`
`AddDateTimeOffsetOutputParam(name)`	`datetimeoffset(7)`	`DateTimeOffset`
`AddVarcharOutputParam(name, size)` (pre-existing)	`varchar(size)`	`string`
`AddNVarcharOutputParam(name, size)` (pre-existing)	`nvarchar(size)`	`string`

After ExecuteNonQuery (or any other execute path), read each value via cmd.Parameters["@Name"].Value. The returned value is object and must be cast to the expected type — or DBNull.Value if the procedure left the parameter unassigned.

Typical use — saved-row id

The most common use is reading back the IDENTITY value after an INSERT:

create procedure dbo.SaveOrder
    @CustomerId int,
    @Total      decimal(19, 2),
    @OrderId    int output            -- assigned after the insert
as
begin
    set nocount on;

    insert into dbo.Orders (CustomerId, Total)
    values (@CustomerId, @Total);

    set @OrderId = scope_identity();
end;

public int SaveOrder(int customerId, decimal total)
{
    return ExecuteCommand(cmd =>
    {
        cmd.UseProcedure("dbo.SaveOrder");
        cmd.AddIntParam      ("@CustomerId", customerId);
        cmd.AddDecimalParam  ("@Total", precision: 19, scale: 2, value: total);
        cmd.AddIntOutputParam("@OrderId");
    });

    // Read back via cmd.Parameters[...].Value — but since ExecuteCommand
    // disposes the cmd, capture it differently:
}

ExecuteCommand disposes the SqlCommand on exit, so to read the output parameter you need to capture it before disposal. Use RunCommand (which lets you keep the cmd around within its lambda) or GetByCommand:

public int SaveOrder(int customerId, decimal total)
{
    return GetByCommand(cmd =>
    {
        cmd.UseProcedure("dbo.SaveOrder");
        cmd.AddIntParam      ("@CustomerId", customerId);
        cmd.AddDecimalParam  ("@Total", 19, 2, total);
        cmd.AddIntOutputParam("@OrderId");

        cmd.Connection.Open();
        try   { cmd.ExecuteNonQuery(); }
        finally { cmd.Connection.Close(); }

        return (int)cmd.Parameters["@OrderId"].Value!;
    });
}

Multiple outputs

A procedure can have any number of output parameters; the same pattern reads each one.

create procedure dbo.GetUserStats
    @UserId      int,
    @PostCount   int     output,
    @LastSeen    datetime2 output,
    @IsAdmin     bit     output
as
begin
    set nocount on;

    select @PostCount = count(*) from dbo.Posts where AuthorId = @UserId;
    select @LastSeen  = max(LoginAt) from dbo.Logins where UserId = @UserId;
    select @IsAdmin   = case when exists (select 1 from dbo.UserRoles
                                          where UserId = @UserId and RoleCode = 'Admin')
                             then 1 else 0 end;
end;

public (int posts, DateTime? lastSeen, bool isAdmin) GetUserStats(int userId)
{
    return GetByCommand(cmd =>
    {
        cmd.UseProcedure("dbo.GetUserStats");
        cmd.AddIntParam              ("@UserId", userId);
        cmd.AddIntOutputParam        ("@PostCount");
        cmd.AddDateTime2OutputParam  ("@LastSeen");
        cmd.AddBitOutputParam        ("@IsAdmin");

        cmd.Connection.Open();
        try   { cmd.ExecuteNonQuery(); }
        finally { cmd.Connection.Close(); }

        var posts    = (int)      cmd.Parameters["@PostCount"].Value!;
        var lastSeen = cmd.Parameters["@LastSeen"].Value is DBNull
                       ? (DateTime?)null
                       : (DateTime?)cmd.Parameters["@LastSeen"].Value;
        var isAdmin  = (bool)     cmd.Parameters["@IsAdmin"].Value!;

        return (posts, lastSeen, isAdmin);
    });
}

`DBNull` handling

Output parameters that the procedure never assigns come back as DBNull.Value, not null. Always test for it before unboxing into a non-nullable type:

var raw = cmd.Parameters["@LastSeen"].Value;
DateTime? lastSeen = raw is DBNull ? null : (DateTime?)raw;

Or use the IsDBNull shorthand pattern:

DateTime? lastSeen = cmd.Parameters["@LastSeen"].Value as DateTime?;

The as DateTime? returns null for both DBNull and a missing value.

Output parameters and result sets

A procedure can return both a result set and output parameters. With Artisan.Orm, this looks like:

return GetByCommand(cmd =>
{
    cmd.UseProcedure("dbo.SaveAndReturnUser");
    cmd.AddTableRowParam("@User", user);
    cmd.AddIntOutputParam("@Status");

    var savedUser = cmd.ReadTo<User>();   // reads the result set

    var status = (int)cmd.Parameters["@Status"].Value!;
    if (status != 0) throw new InvalidOperationException(...);

    return savedUser;
});

Output parameters are populated by SQL Server after the result sets are fully read. If you don't drain the reader, the output values stay at DBNull. ReadTo* and friends drain the current result set automatically; if you pass a custom reader callback, make sure to read everything before checking the parameters.

vs. the return value

The TSQL RETURN <value> statement is read separately via ExecuteCommand / Execute (which captures the auto-added @ReturnValue parameter). Use it for terse single-int status codes; output parameters are richer (any type, any name, any number).