EXEC vs SP_EXECUTESQL in SQL Server
Last Updated :
28 Dec, 2023
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.
|
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.
Similar Reads
DateTime2 vs DateTime in SQL Server
In SQL Server, managing date and time values for various applications ranging from transaction processing to reporting. SQL Server offers two main data types for handling date and time:DateTimeDateTime2While both serve similar purposes, they differ significantly in terms of precision, storage requir
3 min read
Explicit vs Implicit SQL Server Joins
SQL Server is a widely used relational database management system (RDBMS) that provides a robust and scalable platform for managing and organizing data. MySQL is an open-source software developed by Oracle Corporation, that provides features for creating, modifying, and querying databases. It utiliz
5 min read
CREATE TABLE in SQL Server
SQL Server provides a variety of data management tools such as querying, indexing, and transaction processing. It supports multiple programming languages and platforms, making it a versatile RDBMS for various applications. With its robust features and reliability, SQL Server is a popular choice for
4 min read
sp_columns - SQL Server
In SQL Server, managing and understanding database schemas is crucial for effective database administration and development. The sp_columns stored procedure is a valuable tool for retrieving detailed metadata about the columns of a specified table or view. In this article, We will learn about sp_col
6 min read
How to Execute SQL Server Stored Procedure in SQL Developer?
A stored procedure is a set of (T-SQL ) statements needed in times when we are having the repetitive usage of the same query. When there is a need to use a large query multiple times we can create a stored procedure once and execute the same wherever needed instead of writing the whole query again.
2 min read
SQL Server Interview Questions
Data is growing every day, and it plays a big part in making important decisions. To manage this data, we need reliable databases, and SQL Server is one of the most popular options out there. Learning SQL Server can lead to exciting careers as an SQL Specialist or SQL Developer. If youâre preparing
15+ min read
Dynamic SQL in SQL Server
In SQL Server, at times the SQL Queries need to be dynamic and not static, meaning the complete SQL query may be built dynamically at run time as a string using the user inputs and any specific application logic. This can be done in queries run from back-end applications or inside stored procedures.
6 min read
SQL Server CURRENT_USER() Function
The CURRENT_USER() function in SQL Server is a useful tool for identifying the currently logged-in user executing a query. This function helps track user activity and enforce security policies and is often used in auditing scenarios. In this article, We will learn about SQL Server CURRENT_USER() Fun
3 min read
SQL Server Common Table Expressions
SQL Server is a relational database management system (RDBMS) that is used to handle complex data and maintain it in of tabular manner. With the help of SQL Server, one can easily protect their data as it provides various security features. In this article, we are going to explore SQL server's CTE a
8 min read
SQL Server Architecture
Microsoft SQL Server is a widely used relational database management system (RDBMS) that organizations around the world rely on for managing and processing their data. It provides a scalable and reliable platform for managing large volumes of data, supporting a wide range of applications from small-
5 min read