Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL)

sql sql-server dynamic
Ash Machine picture Ash Machine · Feb 14, 2009 · Viewed 207k times · Source

What are the real world pros and cons of executing a dynamic SQL command in a stored procedure in SQL Server using

EXEC (@SQL)

versus

EXEC SP_EXECUTESQL @SQL

?

Answer

Mitch Wheat picture Mitch Wheat · Feb 14, 2009

sp_executesql is more likely to promote query plan reuse. When using sp_executesql, parameters are explicitly identified in the calling signature. This excellent article descibes this process.

The oft cited reference for many aspects of dynamic sql is Erland Sommarskog's must read: "The Curse and Blessings of Dynamic SQL".

Related questions

T-SQL dynamic pivot

Ok I have a table that looks like this ItemID | ColumnName | Value 1 | name | Peter 1 | phone | 12345678 1 | email | [email protected] 2 | name | John 2 | phone | 87654321 2 | email | [email protected] 3 | name | Sarah 3 | phone | 55667788 3 | email | [email protected] Now I need to turn that into …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More