Home > Differences, SQL Tips > EXECUTE (or EXEC) vs sp_executesql

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.

About these ads
  1. Natasha
    October 17, 2011 at 3:15 pm

    great post, really helpfull, thank you all

    if anybody can help to add dynamic ORDER BY in @sql to be executed with sp_executesql

  2. Randy Pitkin
    December 13, 2011 at 8:15 pm

    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.

    • December 14, 2011 at 4:18 am

      Hi Randy,
      Ya, I missed to mention this point, a very important point.
      Thanks for mentioning this!

      ~Manoj

  1. July 29, 2011 at 9:53 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 411 other followers

%d bloggers like this: