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.

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.