Why would the exact same SQL query result with a different execution plan when executed via the sp_executeSQL procedure?

Question

As the title states, I don't understand why the sp_executeSQL would generate a completely different execution plan than running the query from Sql Management Studio.

My query in question will take 3 seconds when run from SQL management Studio, where as the query run in management studio via sp_executeSQL will take 5 minutes.

I've updated statistics, and reviewed indexes, but the fact remained in my head that the execution plan from sp_executeSQL was FAR worse than running the sql directly from my management studio.

So here is my question: Why would the execution plans differ SO much between running the query in these two different ways?

Thanks

Answer 1

see this

basically, there can be multiple [execution] plans for the same procedure

Answer 2

Consider this. When you execute a stored procedure, this procedure will have its own execution plan. When you execute a query statement, again it will have its own execution plan. Now when using sp_executeSQL you are running this stored procedure to execute a query dynamically. So in essence its execution plan is the combination of sp_executeSQL and your query.