SQLCommand Object to Specify Query Hint

Question

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

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
– Panagiotis Kanavos, Commented Feb 22, 2017 at 14:00

dean · Accepted Answer · 2017-02-22 13:56:11Z

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.

answered Feb 22, 2017 at 13:56

dean

10.1k2 gold badges27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sameers Javed Over a year ago

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

Collectives™ on Stack Overflow

SQLCommand Object to Specify Query Hint

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related