Dynamic SQL and stored procedures are among the most important SQL Server components. Both have advantages and disadvantages. That’s why we have prepared this article with a comparison of both the components. It will help you determine when you should apply dynamic SQL or stored procedures for the best result.
Performance
A stored procedure is cached in the server memory, making the code execution much faster than dynamic SQL. Dynamic SQL statements can be stored in DB2 caches, but they are not precompiled. Compilation at run time is a factor making the dynamic SQL performance slower.
Separation of Concerns
In terms of separation of concerns, stored procedures also prevail over dynamic SQL.
Stored procedures allow you to keep your database logic separate from the business logic. If an error occurs in the business logic, you must change the application code. On the other hand, if there is an issue with the database logic, you modify the stored procedure. You don’t need to recompile and deploy the application code anew.
If you use dynamic SQL queries in a client code, you have to update the application code to get rid of errors, recompile, and deploy the application code.
Network Traffic
Stored procedures produce less network traffic than dynamic SQL.
To execute a stored procedure, you must send the procedure name and parameters (if any) only over the network. As for dynamic SQL, you need to send a complete query to execute it. Thus, you are increasing your network traffic, especially if the query is large and complex.
SQL Injection Attacks
Stored procedures are not vulnerable to SQL Injection attacks.
Dynamic SQL queries are vulnerable unless they use parameterized queries. But we can’t use parameterized queries with dynamic SQL if we pass a table or column name as a parameter
The workaround is using the code name function to prevent SQL injection attacks.
Reusability of Cached Query Plans
Stored procedures improve database performance by reusing cached query plans. SQL Server automatically detects the parameters and generates cached query plans. It improves the performance.
As for dynamic SQL, you’ll need to use parameterized queries to increase the reusability of cached query plans.
Note: Only OLTP systems benefit from cached query plan reusability. If you deal with OLAP systems, you should choose a unique plan.
Maintenance
Stored procedures with static SQL are easier to maintain. For example, you can catch a syntax error in a static SQL stored procedure before executing that code. When you have dynamic SQL inside stored procedures, you can’t identify syntax errors before you run that query.
Furthermore, stored procedures are more like functions. We define them once and then can call them anywhere in the script. To update a stored procedure, we only have to update it once in one place. Further, all application parts access the updated version.
However, there is a downside. All those application parts can be affected if something is wrong with the stored procedure.
If you work with dynamic SQL, you might have to write multiple SQL scripts for every application part. On the other hand, updating the script in one place doesn’t affect it in a different place.
Whether to use the stored procedure or dynamic SQL will depend on the application functionality and your individual needs.
Security
If multiple applications access the database, it is more secure to use stored procedures than dynamic SQL.
Stored procedures provide an extra layer of security, whereas the only way to control permissions on dynamic SQL scripts is the user context. All in all, securing dynamic SQL is laborious compared to stored procedures.
Identifying Dependencies
In a relational database, tables have dependencies on other tables in the database.
Let’s consider a scenario where you want to remove a table. Before you do that, you want to find out all of the table dependencies. In simple terms, you want to find the queries that access the table you want to delete. In this case, you can use the sp_depends stored procedure.
However, sp_depends can only detect the dependencies where static SQL is used inside a stored procedure. It won’t detect dynamic SQL in dependencies.
Let us dive deeper here and illustrate the scenario in practice.
We create a database with some dummy data:
CREATE DATABASE deptest;
USE deptest
CREATE TABLE student
(
Id int identity primary key,
Name VARCHAR(50) NOT NULL,
Gender VARCHAR(50) NOT NULL,
Age int
)
INSERT INTO student
VALUES
('James', 'Male', 20),
('Helene', 'Female', 20),
('Sofia', 'Female', 20),
('Ed', 'Male', 20),
('Ron', 'Female', 20)
As you can see, this test database contains a table with test data. Now, we are going to create two stored procedures that access the student table.
The first stored procedure uses static SQL to retrieve all the records from the student table. When you execute the script, it will create a stored procedure called spStatProc inside the deptest database.
USE deptest GO CREATE PROC spStatProc AS BEGIN SELECT * FROM student END
Let’s create another stored procedure containing dynamic SQL to retrieve all records from the student table. This script creates a stored procedure spDynProc inside the deptest database.
USE deptest GO CREATE PROC spDynProc AS BEGIN DECLARE @query NVARCHAR(100) SET @query = 'SELECT * FROM student' EXECUTE sp_execute @query END
Now we have two stored procedures with a dependency on the student table. One of them contains static SQL, and another one contains dynamic SQL.
However, if you execute the sp_depends stored procedure and pass it to the student table as a parameter, it will only retrieve the spStatProc stored procedure. This is because it contains static SQL. The spDynProc stored procedure will be ignored since it contains dynamic SQL.
Execute the following script:
USE deptest GO EXECUTE sp_depends student
It will get the following output:
[table id=40 /]
You can see that sp_depends was not able to report the spDynProc dependency and only reported spStatProc.
Complexity
Stored procedures can get extremely complex if you are using many filters with multiple AND and OR clauses between those filters. On the other hand, with dynamic SQL you can dynamically generate WHERE clauses depending upon the type of filters. This makes dynamic SQL a better choice to implement extremely complex logic.
Conclusion
Overall, stored procedures outperform dynamic SQL. They are faster, easier to maintain, and require less network traffic.
The rule of thumb would suggest using stored procedures in scenarios where you don’t have to modify queries, and those queries are not very complex.
However, if you frequently change table names, column names or the number of parameters in a query, dynamic SQL is the better choice due to the simpler implementation strategy.
Useful Links
- Dynamic SQL versus Stored Procedures
- Don’t fear Dynamic SQL
- Building high performance stored procedures
- Classes on Stored Procedures
