2

I just wonder if there is a way to specify query hint while calling a stored procedure using SQLCommand object?

For example, I need to call something like this

EXECUTE SPName'0068', 40 WITH RECOMPILE

I just wonder, how I can simulate this using SQLCommand object.

        Dim connection As New SqlClient.SqlConnection("server=(local);database=DEV;Persist Security Info=True;uid=sa;pwd=;")
    connection.Open()
    Dim cmd As New SqlClient.SqlCommand
    With cmd
        .Connection = connection
        .CommandText = "Warehouse_AdjustInventory_byWarehouse_u"
        .CommandType = CommandType.StoredProcedure

        .Parameters.AddWithValue("@ProductID", "0068")
        .Parameters.AddWithValue("@WarehouseID", 40)
    End With
    Try
        Dim result As Integer = cmd.ExecuteNonQuery()
        Trace.WriteLine(result)
    Catch ex As Exception
        Trace.WriteLine(ex.Message)
    End Try

    connection.Close()

Does SQLCommand support to supply query hints like With RECOMPILE? thank you

Sameers Javed
  • 342
  • 2
  • 5
  • 16
  • 1
    *Why* do you want to use `WITH RECOMPILE`? This will make your stored procedure *slower*. If the stored procedure uses inefficient techniques like changing WHERE predicates, it would be better to fix the query. If you want the stored procedure to recompile all the time, just add the `WITH RECOMPILE` clause to its definition – Panagiotis Kanavos Feb 22 '17 at 14:00

1 Answers1

1

I believe you can set CommandType to Text and put exactly the statement you want (EXECUTE SPName '0068', 40 WITH RECOMPILE) into CommandText.

However, the real question is why the WITH RECOMPILE at all, as it will recompile all the queries inside the procedure on every execution? Examine the execution plan and try to identify individual statements which would benefit from recompilation. Then use the OPTION(RECOMPILE) hint with these queries only.

dean
  • 9,960
  • 2
  • 25
  • 26
  • That is somehow correct, but I forgot to mention that I do want to use the regular stored procedure with parameters, instead of using the TEXT as the CommandType. I know using the CommandType=Text will work, and I already did that, but its more like educational to know if it is possible to provide such hints with regular SP call with CommandType=StoredProcedure – Sameers Javed Feb 24 '17 at 06:31