sp_executesql vs execute in SQL Server

In this post “SP_EXECUTESQL vs Execute”, we are going to compare sp_executesql and execute in SQL Server. Apart from differences, we will also discuss the similarities between sp_executesql and execute commands. Lets start with few questions like, What is the difference between Execute and sp_ExecuteSQL? How can we execute a T-SQL String? Similarities between Execute and sp_ExecuteSQL. Execute and sp_ExecuteSQL common features. Benefits of sp_ExecuteSQL. Which is better to use sp_ExecuteSQL or EXEC? Execute vs sp_ExecuteSQL.

Let’s discuss these points one by one.

Difference between sp_ExecuteSQL vs Execute

sp_ExecuteSQL

sp_ExecuteSQL is also used to execute a T-SQL string in SQL Server and points are below:

• It allows parametrization and hence more secure than EXEC command to execute a SQL string dynamically. It’s tough to inject.
• We don’t need to cast the parameter values as like we need in EXEC command. We simply put the parameter name in T-SQL string as it is and in “Parameter definition”, we put the name and data type of the parameter. For example SET @SQLQuery = ‘SELECT EMpName FROM EMPLOYEE WHERE EmpID = @EmpID. And in parameter definition, we put the name and data type of all the parameters.
• It’s more likely to reuse the execution plan from plan cache as it allows parametrization of the SQL query and so actual text of the SQL query does not change for each execution. If there is only change in parameter values and T-SQL string remains constant, SQL Server query optimizer can reuse the execution plan it has generated at first execution of this query.
• In this case T-SQL string is built only once.
• Variable type used to hold the dynamic T-SQL string can be of NCHAR or NVARCHAR type only.

Execute

Execute or Exec command is used to execute a SQL query written as a string in SQL Server. Let’s discuss some points for Exec:

• It does not allow parametrization of T-SQL String and hence it’s more open to inject for someone with bad mind.
• Needs typecasting the parameter values as character or Unicode character to concatenate with T-SQL string, except character or Unicode values. For example SET @SQLQuery = ‘SELECT EMpName FROM EMPLOYEE WHERE EmpID = ‘ + CAST(@EmpID AS VARCHAR(10)).
• Lack of reusability of cached query plan mostly in case of complex T-SQL statements, due to frequent change in parameter values which have been concatenated in T-SQL string as it’s part.
• T-SQL string is rebuilt for each execution.
• Variable type used to hold the dynamic T-SQL string can be of CHAR, NCHAR, VARCHAR or NVARCHAR type.

Similarities between Execute and sp_ExecuteSQL

Below are some common points for both the commands;

• Both can be used to execute a T-SQL String in SQL Server.
• T-SQL statement gets compiled and parsed or even checked for error when EXEC or sp_ExecuteSQL command gets executed.
• The T-SQL string gets executed in it’s own batch which is different than the batch that contains this EXEC or sp_ExecuteSQL.
• Any variable or temporary objects declared or created inside the T-SQL batch is only accessible in it’s own batch and not in the batch which contains these commands.
• Similarly any variable declared in the batch which contains the EXEC or sp_ExecuteSQL statement will not be accessible inside the EXEC or sp_ExecuteSQL’ s T-SQL string.
• If you have used a USE statement to change the scope of the database context, this change will only lasts till EXEC or sp_ExecuteSQL statement gets executed.

Benefits of sp_ExecuteSQL

As we have discussed above sp_ExecuteSQL allows parametrization of the T-SQL string and so it’s better to use it over EXEC to avoid SQL injection. Another benefit of using sp_ExecuteSQL is the reuse of execution plan if only change is in parameter values. It’s better to use sp_ExecuteSQL also because we don’t need to type cast the parameter values in string too.

If i have missed anything important in difference or similarity, just put your points and i will update this post with your point and name.