C# ado.net stored procedure with recompile hint

Question

Is there a way to add OPTION (RECOMPILE) in C# while executing stored procedure by System.Data.SqlClient?

What I'm looking for in my imagination would be something like

using (SqlConnection sqlConn = new SqlConnection(CONN_STR))
{
    sqlConn.Open();
    using (SqlCommand cmd = new SqlCommand("usp_xyz OPTION (RECOMPILE)", sqlConn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("x", x);
        cmd.ExecuteNonQuery();
    }
}

Recompile needs to be specified at the server side, not the client (so the server knows that it can’t just re-use an execution plan). — Steve Todd, Aug 18 '22 at 09:10
SQL Stored procedures are not compiled. Stored Procedures are parsed at execution time. — jdweng, Aug 18 '22 at 10:25
@jdweng That's not completely correct. They are parsed, but not fully bound until runtime if there are binding errors. The actual query plan compilation does also happen at runtime (assuming it's not cached), but from the parse tree, not from the bare SQL text. — Charlieface, Aug 18 '22 at 13:16

score 0 · Accepted Answer · answered Aug 21 '22 at 13:08

Yes, you can use the EXEC... WITH RECOMPILE syntax, but you must do it as an ad-hoc batch, and therefore specify all parameters. You cannot use this with CommandType.StoredProcedure.

using (SqlConnection sqlConn = new SqlConnection(CONN_STR))
{
    sqlConn.Open();
    using (SqlCommand cmd = new SqlCommand("EXEC usp_xyz @x = @x WITH RECOMPILE;", sqlConn))
    {
        cmd.Parameters.Add("@x", SqlDbType.SomeType, SomeLength).Value = x;
        cmd.ExecuteNonQuery();
    }
}

If you want, you could use sp_recompile, but this has different semantics: it does not just generate a new plan for this run, it discards the old plan for all future runs of the procedure.

using (SqlConnection sqlConn = new SqlConnection(CONN_STR))
{
    sqlConn.Open();
    using (SqlCommand cmd = new SqlCommand("sp_recompile", sqlConn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@objname", SqlDbType.NVarChar, 776).Value = "dbo.usp_xyz";
        cmd.ExecuteNonQuery();
    }

    using (SqlCommand cmd = new SqlCommand("usp_xyz", sqlConn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@x", SqlDbType.SomeType, SomeLength).Value = x;
        cmd.ExecuteNonQuery();
    }
}

C# ado.net stored procedure with recompile hint

1 Answers1