EXECUTE (or EXEC) vs sp_executesql
–> EXECUTE:
As per MS BOL EXECUTE executes a command string or character string within a TSQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure. The TSQL query can be a direct string or a variable of char, varchar, nchar, or nvarchar data type.
–> sp_executesql:
As per MS BOL sp_executesql executes a TSQL statement or batch that can be reused many times, or one that has been built dynamically. The TSQL statement or batch can contain embedded parameters. The SQL query is a Unicode string or a Unicode variable that contains a Transact-SQL statement or batch. Here the variable datatype is restricted to Unicode nchar or nvarchar only. If a Unicode constant (SQL string) is used then the it must be prefixed with N.
–> Main difference performance wise: sp_executesql is generally preferred over EXEC() when executing dynamic T-SQL. sp_executesql works by creating a stored procedure using the specified query, then calling it using the supplied parameters. Unlike EXEC(), sp_executesql provides a mechanism that allows you to parameterize dynamic T-SQL and encourage plan reuse. A dynamic query that is executed using sp_executesql has a much better chance of avoiding unnecessary compilation and resource costs than one ran using EXEC().
Let’s check the difference with a simple example using [AdventureWorks] database’s [Person].[Contact] table:
USE [AdventureWorks] GO DECLARE @str NVARCHAR(1000) DECLARE @fName NVARCHAR(20) DECLARE @ePromo INT SET @fName = N'James' SET @ePromo = 1 --// Using EXECUTE or EXEC SET @str = N' SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone, EmailPromotion FROM Person.Contact WHERE FirstName = ''' + @fName + ''' AND EmailPromotion = ' + CAST(@ePromo as NVARCHAR(20)) PRINT @str EXEC (@str) GO --// Using sp_executesql DECLARE @str NVARCHAR(1000) DECLARE @fName NVARCHAR(20) DECLARE @ePromo INT DECLARE @paramList NVARCHAR(500) SET @paramList = N'@fNameParam NVARCHAR(20), @ePromoParam INT' SET @str = N' SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone, EmailPromotion FROM Person.Contact WHERE FirstName = @fNameParam AND EmailPromotion = @ePromoParam' SET @fName = N'James' SET @ePromo = 1 EXECUTE sp_executesql @str, @paramList, @fNameParam=@fName, @ePromoParam=@ePromo -- When the match is LIKE SET @str = N' SELECT ContactID, FirstName, MiddleName, LastName, EmailAddress, Phone, EmailPromotion FROM Person.Contact WHERE FirstName LIKE ''%'' + @fNameParam + ''%'' AND EmailPromotion = @ePromoParam' SET @fName = N'James' SET @ePromo = 0 EXECUTE sp_executesql @str, @paramList, @fNameParam=@fName, @ePromoParam=@ePromo GO
Security Note:
Before you call EXECUTE or sp_executesql with a character string, validate the character string. Never execute a command constructed from user input that has not been validated. For more information, see SQL Injection.
great post, really helpfull, thank you all
if anybody can help to add dynamic ORDER BY in @sql to be executed with sp_executesql
Misses the point; sp_executeSQL maintains an execution history increasing performance.
Further, the use of variables allows for input validtion before execution. and limits the risk to injection.
Hi Randy,
Ya, I missed to mention this point, a very important point.
Thanks for mentioning this!
~Manoj