We know sp_executesql and exec are two different approaches to execute dynamic T-SQL statements or queries. Dynamic SQL is a program which allows you to make SQL statements dynamically.
It provides more flexibility once you do not know the parameters or table name/ column names. Based on the research, earlier developers used to execute their dynamic SQL statements using exec commands in the past, which is not a wrong way though.
However, the main disadvantage of executing dynamic statements by using exec command is possible SQL Injection, which is a main threat to data security, where in later invented sp_executesql is pretty safe in that case. In the text below, we have a comparison between sp_executesql and exec.
Query overview
| EXEC (EXECUTE) | sp_executesql |
| It supports VARCHAR and NVARCHAR type strings. | It only supports NVARCHAR type string |
| The parameterization is not possible. | The parameterization is possible. |
| You will not be able to use the output variable. | You will be able to set a value to a output variable |
| Risk of SQL injection is high. | Risk of SQL injection is less |
| EXEC does not push a plan to be cached. | sp_executesql push the plan to be cached. |
| An exec can wastes many space in the plan cache. | sp_executesql gets cached like a stored procedure with no wasting white spaces, if the parameterization is used. |
| More likely an Ad-hoc statement, so it does not look at optimizer during execution and obtains compilation every time. | It reutilizes the cached plan, so it does not compile every time. |
| If you have table names being queried dynamically, so ad-hoc method is going to work better in that case. | A table name will not be able to be passed dynamically as a parameter, where you have to create that part ad-hoc. |
| It does not support prepared statements. | It does not prepare or parse the SQL statement prior to execution. |
In the table above, we have explained a comparison between sp_executesql and exec. Now, can you conclude what the differences of sp_executesql and exec? If you still are confused and need more information, let us continue reading the next step. We are going to explain it once again regarding sp_executesql and exec.
EXEC AND SP_EXECUTESQL – HOW ARE THEY DIFFERENT?
sp_executesql is also known as “Forced Statement Caching.”
- It allows for the statements to be parameterized.
- It only allows the parameters where SQL Server will normally allow parameters. But, this string is able to be built by using forms of dynamic constructs. Next, we are going to give more details.
- It has typed variables or parameters strongly. Also, this is able to reduce injection and offer several performance benefits.
- It makes a plan on the first execution and the subsequent executions reutilize this plan.
EXEC is also known as “Dynamic String Execution.”
- It allows any construct to be built.
- This treats the statement similarly to an adhoc statement. It means that the statement goes through the same process which adHoc statements do. They are parsed, maybe parameterized and possibly deemed safe for subsequent executions to reutilize.
- It does not have typed parameters strongly in the adhoc statement. So, it is able to cause the issues when the statements are executed.
- It does not push a plan to be cached.
- This is able to be a pro in which SQL Server can make a plan for each execution.
- This is able to be a con in that SQL Server requires to recompile or optimize for each execution.
EXEC
EXEC executes a command string/ character string within a TSQL statement/batch, or in one of the following: the user defined stored procedure, the system stored procedure, the extended stored procedure or the scalar valued user-defined function. The TSQL query is able to be a direct string or a variable of nchar, char, varchar, or nvarchar data type.
Simply, we are able to say that the EXEC command is used to execute a stored procedure, or a SQL string passed to it. Also, you are able to use full command EXECUTE which is the same as EXEC. To execute a stored procedure by using EXEC pass the procedure name and parameters, you have to refer to the T-SQL script to execute a stored procedure.
Besides, you are also able to assign the value returned by a stored procedure to a variable. Please refer to the T-SQL script. To execute a string, you have to construct the string and pass it to the EXEC command. Do not forget to refer to the example which executes a string.
You have to note that constructing a string from a variable and executing it by using EXEC command will be able to inject unwanted code. Apparently, there are some methods to avoid SQL injection. Next, we are going to review those methods in another article.
sp_executesql
sp_executesql is an extended stored procedure which is able to be used to execute dynamic SQL statements in SQL Server. You need to pass the SQL statement and definition of the parameters used in the SQL statement and then you are able to set the values to the parameters used in the query.
The sp_executesql executes a TSQL statement or batch which can be reutilized many times, or one that has been built dynamically. The TSQL statement is able to contain embedded parameters.
The SQL query is a Unicode string (Unicode variable) which contains a Transact-SQL statement/ batch. Here, the variable data type is restricted to Unicode nvarchar or nchar only. If a Unicode constant (SQL string) is used, so it must be prefixed with N.
MAIN DIFFERENCE PERFORMANCE
Generally, sp_executesql is preferred over EXEC once executing dynamic T-SQL. the sp_executesql always works to make a stored procedure by using the specified query. Then, call it by using the supplied parameters.
The sp_executesql offers you to parameterize dynamic T-SQL and then encourage plan reuse. Of course, this is unlike EXEC. A dynamic query which is executed by using sp_executesql has a lot of chance to avoid unnecessary compilation and resource costs than one run by using EXEC.
AUTHOR BIO
On my daily job, I am a software engineer, programmer & computer technician. My passion is assembling PC hardware, studying Operating System and all things related to computers technology. I also love to make short films for YouTube as a producer. More at about me…
Leave a Reply