SQL Server Performance Forum – Threads Archive

Will sp_ExecuteSQL help my plight?

Hi All, Currently we are generating Dynamic SQL Statements for selecting, updating, and inserting records from an ASP page based on what data will be worked with on that page. Effectivly we are sending "update database.dbo.table set field = ‘value’,field1=’value1′ where fieldPK = ‘ValuePK’" down the ADO pipe to the SQL 2000 server. Converting to pure Stored Procedures would be better, however it is not possible at this time. I am looking for an interm solution. sp_ExecuteSQL looks like it might be the way to go. I could build and pass "sp_ExecuteSQL N’update database.dbo.table set field = @v,[email protected] where fieldPK = @vpk’,N’@v int, @v1 int, @vpk int’,@v = 0, @v1 = 1, @vpk = 2" down the pipe. I am assuming a lot of things here: 1. The first example will not reuse an execution plan when ‘ValuePK’ changes to ‘ValuePKDifferent’ and is passed to SQL Server again. 2. sp_ExecuteSQL will reuse an execution plan when @vpk changes, or when @v changes for that matter. How is my thinking on this one?
Does fully qualifing the tables matter either way?
Is it worth the effort?
How much worse is sp_ExecuteSQL than Pure SPs?
How much worse is raw SQL vs Pure SPs? Thanks!
-DarrenM

---

http://www.sommarskog.se/dynamic_sql.html gives a very good discussion on that topic.
Frank
http://www.insidesql.de
http://www.familienzirkus.de

---

]]>