EXEC vs SP_EXECUTESQL in SQL Server

In SQL Server, dynamic SQL is a method to construct a SQL query at run time. For executing dynamic SQL queries there is a command called EXEC or EXECUTE and then the built-in stored procedure called sp_executesql. Both are used in running dynamic SQL queries in string format or using SQL variables.

Both EXEC and sp_executesql are used to execute SQL statements dynamically, but there are some key differences between these two. In this article let us delve into these 2 different dynamic SQL execution methods, their differences, and their purpose.

EXEC Command

The EXEC command is used to execute a dynamic SQL query or a Stored Procedure. It is generally written as EXEC but can be also written as EXECUTE.

Syntax:

EXEC | EXECUTE ({@string_variable | [ N ]'tsql_string' })

The EXEC command can be used to execute a SQL query stored in a string variable or directly as a SQL query string.

Examples: EXEC Command

1. Dynamic SQL

Below is an example of how the ‘EXEC‘ command can be used to execute dynamic SQL queries:

Declare @sqlquery nvarchar(1000),@Coursename varchar(20)
Set @Coursename='Mathematics'
Set @sqlquery='Select * from Students where Course=' + CHAR(39) + @Coursename + CHAR(39)
EXEC(@sqlquery)

Here the select statement is used to fetch data from ‘Students’ table based on the ‘Course’ column value, which can be set dynamically. The dynamic query is created and saved in the SQL variable ‘@sqlquery’ and executed using the ‘EXEC’ command.

2. Stored Procedure

EXEC command can also be used to run a Stored Procedure and below is a simple example of how it is used.

EXEC SelectAllCustomers;

Here the EXEC command is used to run a Stored Procedure named ‘SelectAllCustomers’.

SP_EXECUTESQL command

The sp_executesql is a SQL Server system Stored Procedure, used to execute dynamic SQL queries with parameter values.

Syntax:

sp_executesql N'statment', [ {parameters definitions}, {parameters values} ]

The sp_executesql has 3 parts. First, the SQL statement, then the parameter definition and the third part is the parameter value.

Example :

Declare @sqlquery nvarchar(1000),@Coursename varchar(20)
Set @sqlquery='Select * from Students where Course=@eCourse' 
Set @Coursename='Mathematics'
Exe sp_executesql @sqlquery, N'@eCourse varchar(12)', @eCourse =@Coursename

In the above example, dynamic SQL query is created with a parameter value ‘@eCourse’. When the dynamic SQL is executed using sp_executesql, the value from the variable ‘@Coursename’ is assigned to the parameter.

EXEC VS SP_EXECUTESQL

Key Differences

The EXEC and sp_excutesql are both used to run dynamic sql queries. But sp_executesql can be used for sending parameter values to the query at runtime. This will help in avoiding sql injection and executing the dynamically created queries safely.

Performance Considerations

The sp_executesql generates execution plans which can be reused by SQL Server, while EXEC command leads to single-use memory wasting plans and is not reused.

EXEC	SP_EXECUTESQL
1. The EXEC is SQL command, used to execute dynamically created SQL Queries.	sp_executesql is a system stored procedure, also used for executing dynamic SQL Queries.
2. EXEC statement directly executes the dynamic SQL query without parameterization.	sp_executesql supports parameterization and can pass parameters using placeholder variables.
3. With EXEC command, the risk of SQL injection is very high without parameter values, since the user input is directly embedded into the SQL.	Since the sp_executesql uses parameter values, it is safer and prevents SQL injections.
4. The SQL Query used in EXEC command does not have re-usable cached query plan. So, the dynamic query string is built on each execution.	sp_executesql uses reusable execution plans from the cached query plans. So once the query is built, it is re-used on each execution.
5. The EXE command compiles the dynamic query and executes immediately.	The dynamic query using sp_executesql is compiled separately and then executed.

Conclusion

We have seen how the dynamic query is created and executed using the 2 different commands, EXEC and sp_executesql. Both commands can be used to execute dynamic SQL queries, but sp_excutesql is more secure and optimized to save memory. Also we need to use the right command based on the dynamic query, since if the query has parameter values to be passed at run time, then sp_executesql is the right choice.