SQL Server Performance

using sp_executesql

Discussion in 'T-SQL Performance Tuning for Developers' started by cwijeratna, Sep 12, 2005.

  1. cwijeratna New Member

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

    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
  3. FrankKalis Moderator

Share This Page