SQL Server Performance Forum – Threads Archive

using sp_executesql

I’v heard that using sp_executesql is better than using execute to run a dynamic SQL. What I want to know is whether there is any performance deferecen in running the dynamic SQL with *passing parameters to the SQL when calling the sp_executesql *create the dynamic sql with the parameter already set inside the sql string and just executing the sp_executesql passing just the sql string which is better or is there any diference Thanks.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

I think passing parameter is the best option. http://support.microsoft.com/default.aspx/kb/243588 says
The sp_executesql stored procedure is called by the OLE DB provider or ODBC driver when parameter markers are used in an application. However, it may also be called directly by the application or in another stored procedure to explicitly parameterize ad-hoc queries. This can be very useful in applications or batch files where the EXECUTE statement is used to execute dynamic SQL statements. Unlike sp_executesql, the EXECUTE statement does not permit parameterization. This limits the chance of query plan reuse. For more information, see the "sp_executesql (T-SQL)" and "Using sp_executesql" topics in SQL Server 7.0 Books Online. as it says, it enhaced the use of the queary execution plan —————————————-
http://spaces.msn.com/members/dineshasanka

http://www.sommarskog.se/dynamic_sql.html —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>