Stored Procedure Return Model Errors

Why errors occur

On SQL Server, the efrpg tool executes each stored procedure and table-valued function with SET FMTONLY ON to discover the result set shape without running the body. This can fail for a number of reasons:

• Temp tables — the SP creates a #TempTable and selects from it; the engine cannot infer the schema at parse time
• Dynamic SQL — the return shape is built at runtime via EXEC or sp_executesql
• Missing permissions — the connection user cannot access an underlying object referenced by the SP
• Linked servers — the SP queries a remote server that is unavailable at generation time
• Conditional logic — the SP has branching paths that only materialise at runtime

This page is about SQL Server, which is the only database where discovery is done by executing anything. The others differ: PostgreSQL reads function result shapes out of pg_proc, and MySQL, Oracle and SQLite produce no return models at all - you get a caller and have to supply the shape yourself, with Settings.StoredProcedureReturnTypes or a hand-written class.

v4 note. The reading now happens in the efrpg tool, in its own process. The tool sends back the error message, and the template reconstructs a plain new Exception(message) before calling your delegate. So matching on ex.Message works exactly as before, but matching on the exception type (ex is SqlException) or reading ex.Number no longer does. If you carried such a check over from v3, switch it to a message test.

Default behaviour

By default, when an exception occurs the generator stores the error message on the stored procedure and renders it as a comment on every generated method for that SP — in the interface, the DbContext implementation, and the Fake DbContext.

// Unable to determine return model for 'GetRecentOrders'. Error: Invalid object name '#Results'.
public int GetRecentOrders(int customerId)
{
    ...
}

The SP is still generated (as an int-returning method), so your project compiles. The comment tells you exactly which SP is affected and why.

The ReadStoredProcReturnObjectException delegate

You can customise this behaviour by overriding the delegate in your Database.tt:

Settings.ReadStoredProcReturnObjectException = delegate(Exception ex, StoredProcedure sp)
{
    // your logic here
};

The delegate receives:

Parameter	Type	Description
ex	Exception	The exception thrown while reading the return model
sp	StoredProcedure	The stored procedure being processed

Useful properties on sp:

Property	Type	Description
sp.NameHumanCase	string	Pascal-cased SP name (e.g. GetRecentOrders)
sp.DbName	string	Raw database name (e.g. get_recent_orders)
sp.Schema.DbName	string	Schema name (e.g. dbo)
sp.ReturnModels	List<List<DataColumn>>	Add to this to manually define the return shape
sp.Error	string	Set this to emit a comment; set to null to suppress it
ex.Message	string	The error from the database engine

---

Recipes

1. Keep the default — show the error as a comment

This is what happens out of the box. No configuration needed.

// Default — no override required:
// sp.Error = ex.Message;

---

2. Suppress the error comment entirely

If you don't want any comment generated for failing SPs:

Settings.ReadStoredProcReturnObjectException = (ex, sp) => { };

---

3. Manually define the return model for a known SP

The most powerful option. If you know what the SP returns (because you wrote it), you can hand-craft the DataColumn list so the generator produces a proper typed return model — exactly as if the database had described it successfully.

Settings.ReadStoredProcReturnObjectException = (ex, sp) =>
{
    if (sp.NameHumanCase.Equals("GetRecentOrders", StringComparison.OrdinalIgnoreCase))
    {
        sp.ReturnModels.Add(new List<DataColumn>
        {
            new DataColumn("OrderId",    typeof(int))      { AllowDBNull = false },
            new DataColumn("CustomerId", typeof(int))      { AllowDBNull = false },
            new DataColumn("OrderDate",  typeof(DateTime)) { AllowDBNull = false },
            new DataColumn("Total",      typeof(decimal))  { AllowDBNull = true  },
        });
    }
    else
    {
        // Fall back to showing the error comment for everything else
        sp.Error = ex.Message;
    }
};

This produces a fully typed return model:

public List<GetRecentOrdersReturnModel> GetRecentOrders(int customerId) { ... }

public class GetRecentOrdersReturnModel
{
    public int OrderId { get; set; }
    public int CustomerId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal? Total { get; set; }
}

---

4. Handle multiple SPs

Settings.ReadStoredProcReturnObjectException = (ex, sp) =>
{
    switch (sp.NameHumanCase.ToLower())
    {
        case "getrecentorders":
            sp.ReturnModels.Add(new List<DataColumn>
            {
                new DataColumn("OrderId",   typeof(int))      { AllowDBNull = false },
                new DataColumn("OrderDate", typeof(DateTime)) { AllowDBNull = false },
            });
            break;

        case "getproductsummary":
            sp.ReturnModels.Add(new List<DataColumn>
            {
                new DataColumn("ProductId", typeof(int))    { AllowDBNull = false },
                new DataColumn("Name",      typeof(string)) { AllowDBNull = false },
                new DataColumn("Stock",     typeof(int))    { AllowDBNull = false },
            });
            break;

        default:
            sp.Error = ex.Message; // show comment for unknown failures
            break;
    }
};

---

5. Filter by error type

Settings.ReadStoredProcReturnObjectException = (ex, sp) =>
{
    // Temp table SPs are expected to fail — suppress the noise
    if (ex.Message.IndexOf("Invalid object name '#", StringComparison.OrdinalIgnoreCase) >= 0)
        return; // no comment, no return model — generates as int

    // Everything else is unexpected — show the error
    sp.Error = ex.Message;
};

---

See also

• Settings.ReadStoredProcReturnObjectCompleted — fires after a SP's return model is successfully read; use it to add, remove, or modify columns on the return model.
• Settings.StoredProcedureReturnTypes — a dictionary that lets you override the return type name for a specific SP.
• Settings.MergeMultipleStoredProcModelsIfAllSame — collapses multiple identical result sets into one.