SQL Server Performance Forum – Threads Archive

How to remove cursor and dynamic sql from proc

I currently have a stored procedure that has a cursor and dynamic sql inside of it. I would like to convert this over to just a proc if possible.
Basically we have a simple select statment and then we call the dynamic updated statement.
The speed of this proc is terrible becuase it is doing a row by row update.
Any advice on how to change this would be sweet.
Thanks
Matt

---

Try posting your query here and we will have a look at it.
KH

---

If you use the EXEC command to dynamically build the SQL statment, then SQL Server has to parse and compile the statement. You certainly lose the benefit of a precompiled query plan. http://www.4guysfromrolla.com/webtech/102300-1.shtml a good reference. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

You might look into sp_ExecuteSQL, and setting up proper parameters to handle criteria – often gives you the benefit of execution plans getting re-used, instead of a compilation. But it always depends on the actual queries being executed, whether you will get such benefits.

---

Hi,
If your query has got dynamic table name or orderr by clause id dynamic or in few cases if your where condition is dynamic, you need to go for dynamic SQL which is unavoidable.
In this scenario, as adriaan suggested, execute your dynamic sql using sp_executeSQL which will be helpful to reuse the plan.

---

]]>