Difference between sp_execute and sp_executesql and exec

We know sp_execute , sp_executesql and exec are three different approaches to execute dynamic T-SQL queries or statements. Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. It gives more flexibility when you don’t know the parameters or table name or column names etc etc. Earlier developers used to execute their dynamic SQL statements using EXEC or EXECUTE command in past, which is not a wrong method though. But the main disadvantage of executing dynamic queries using EXEC command is possible of SQL Injection, which is a major threat to data security, where in later invented sp_executesql and sp_execute are pretty safe in that case. Let’s have a quick comparison between them.

EXEC or EXECUTE sp_executesql sp_execute
It supports VARCHAR and NVARCHAR type string It supports NVARCHAR type string only It supports NVARCHAR type string only
parameterization is not possible. parameterization is possible. parameterization is possible.
You can not use output variable You can assign a value to a output variable You can assign a value to a output variable
Risk of SQL injection is high Risk of SQL injection is less Risk of SQL injection is less
It does not force a plan to be cached It forces the plan to be cached at first execution It forces the plan to be cached at first execution
An EXEC call wastes a lot of space in the plan cache. It gets cached like a stored procedure without wasting white spaces , if parameterization is used It gets cached like a stored procedure without wasting white spaces , if parameterization is used
More likely an Ad-hoc statement, so it does not look at optimizer during execution and gets compiled every time. It re-uses the cached plan and does not compile every time It re-uses the cached plan and does not compile every time
If you have table names being queried dynamically then ad-hoc method will work better in that case A table name can not be passed dynamically as a parameter, where in you have to make that part ad-hoc A table name can not be passed dynamically as a parameter, where in you have to make that part ad-hoc
Does not support prepared statement Does not prepare or parse the SQL statement prior to execution sp_execute is a system stored procedure executes prepared statements from client APIs like ADO, OLE DB, ODBC etc

EXEC() OR EXECUTE():

exec_plan_usage_sql_serverIn above query, I have a same SQL statement executed twice with two different parameters for “JobTitle”. As you can see the second one did not use the cached plan which was cached during the first execution and generated a new plan.

sp_executesql:

sp_executesql_plan_usage_sql_serverI did the same exercise. Executed the same SQL statement twice using two different  parameters now lets see the plan usage different. We could see this is using the cached plan rather generating a new plan.

sp_execute:

sp_execute behaves same like sp_executesql except it has two other dependent system stored procedures sp_prepare and sp_unprepare to prepare and dispose the T-SQL respectively.

sp_prepare: It takes the T-SQL statement and required t-sql parameters as two input parameters and generates a handle type INT after parsing and preparing the statement.

sp_execute: It takes the handle and parameter values to execute the prepared document.

sp_unprepare: It takes the handle as an input parameter to dispose the prepared statement.

sp_execute_plan_usage_sql_server

Here in above query, I have prepared the T-SQL statement first then using the same handle executed the SQL statement twice with two different parameters. We can see this is also using the cached plan rather generating a new plan every time.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

3 thoughts on “Difference between sp_execute and sp_executesql and exec

  • December 29, 2015 at 10:42 pm
    Permalink

    My friend, before asking a question, I want to give you congratulations! Very good your post.
    My dear, your text is very clear about the differences between the three ways to run a query dynamically.
    Of all the examples you gave, I wish you could tell me how do I pass as a parameter the name of a table. For example, I have the following code below:

    declare @sql nvarchar(50)
    declare @total int
    declare @table nvarchar(40) = ‘name_table’
    set @sql=’select @total = count(*) from @table’
    exec sp_executesql @sql,N’@total int OUT’,@total output
    select @total

    As it stands, this code and returns an error message that says: You must declare the table variable “@table”. So how can I do this apart from ad-hoc manner.

    Thank you so much for your patience.
    Hugs

    Reply
    • March 5, 2016 at 1:26 pm
      Permalink

      DECLARE @Sql NVARCHAR(MAX),@para NVARCHAR(MAX),@Id INT,@Name VARCHAR(MAX),@tab NVARCHAR(MAX)=’tblEmployee’

      SET @Sql=’SELECT @Name=Name FROM ‘+@tab+’ WHERE Id=@Id’

      SET @Para=N’@Id INT,@Name VARCHAR(MAX) OUTPUT’
      SET @Id=1

      EXEC sys.sp_executesql @Sql,@Para,@id,@name OUTPUT
      PRINT(@SQL)

      SELECT @Name AS Name

      Reply
    • September 6, 2017 at 4:30 pm
      Permalink

      On sql server 2008 you can wite
      declare @table nvarchar(40) = ‘name_table’
      On sql server 2005
      declare @table nvarchar(40)
      set @table= ‘name_table’

      Reply

Leave a Reply

%d bloggers like this: