Dynamic SQL vs Stored Procedure

Ben Richardson SQL Server 20,979 views
Total: 10 Average: 3.1

Dynamic SQL and stored procedures are two of the most important components of SQL Server. In this article, we will look at the advantages and disadvantages of each of them and when to use them.

Performance

Everyone knows the answer to this question. Stored procedures beat dynamic SQL in terms of performance. A stored procedure is cached in the server memory and its execution is much faster than dynamic SQL. If all the remaining variables are kept constant, stored procedure outperforms dynamic SQL.

Separation of Concerns

In terms of separation of concerns, stored procedures beat dynamic SQL hands down.

Stored procedures allow you to keep your database logic separate from your business logic. Therefore, if an error occurs in your business logic, you only have to change your application code. Conversely, if there is an issue with your database logic, only your stored procedure needs to be modified. In addition, if a stored procedure is updated, the application code does not need to be recompiled and deployed.

If you use dynamic SQL queries in your client code, you will have to update the application code if an error occurs in the SQL query. This means that you will have to recompile and deploy the application code.

Network Traffic

Stored procedures produce less network traffic then dynamic SQL because executing a stored procedure requires only the procedure name and parameters (if any) to be sent over the network.

Executing dynamic SQL requires the complete query to be sent across the network, increasing network traffic, particularly if the query is very large.

SQL Injection Attacks

Stored procedures are not vulnerable to SQL Injection attacks.

Dynamic SQL queries are vulnerable to SQL injection attacks if parameterized queries are not used, and parameterized queries cannot be used with dynamic SQL if a table or column name is passed as a parameter.

In this case, the workaround is that the code name function can be used to prevent SQL injection attacks.

Reusability of Cached Query Plans

Stored procedures improve database performance as they allow cached query plans to be reused. In the case of dynamic SQL, you will have to use parameterized queries to increase cached query plan reusability. In the absence of parameterized query plans, SQL server automatically detects parameters and generates cached query plans resulting in improved performance.

It is pertinent to mention here that only OLTP systems benefit from cached query plan reusability. In the case of OLAP systems, the choice of the optimizer change, OLAP system benefits from the unique plan.

Maintenance

Stored procedures with static SQL are easier to maintain. For example, in the case of static SQL in a stored procedure, syntax errors can be caught before being run. In the case of dynamic SQL inside stored procedures, syntax errors cannot be caught before the query execution.

Furthermore, stored procedures are more like functions, they are defined once and then can be called anywhere in the script. Therefore, if you want to update a stored procedure, you only have to update it at one place. All the application parts calling the stored procedure will have access to the updated version. However, a downside is that those application parts can also be affected where you don’t want the updated stored procedure. In case of dynamic SQL you might have to write SQL script at multiple places, but in such cases, updating script at one place doesn’t affect the other. A decision between using a stored procedure and dynamic SQL depends upon the application functionality.

Security

If multiple applications access database, it is more secure to use stored procedures than dynamic SQL.

Stored procedures provide an extra layer of security, whereas the user context is the only way to control permissions on dynamic SQL scripts. 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.

Consider a scenario where you want to remove a table but, before you do that, you want to find out all of the table dependencies. Or in simple terms, you want to find the queries that access the table that you want to delete. In these cases, you can use the sp_depends stored procedure.

However, sp_depends can only detect those dependencies where static SQL is used inside a stored procedure. In the case of dynamic SQL being dependent upon a table, that dependency cannot be detected by the sp_depends stored procedure. Let us see this in action with the help of a simple example.

Preparing Dummy Data

Let’s create some dummy data to help explain the concept of dependencies in static and dynamic SQL.

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)

Now we have a test database containing a table and some test data. Now let’s 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:

USE deptest
GO
CREATE PROC spStatProc
AS
BEGIN
	SELECT * FROM student
END

Execute the script above. This script creates a stored procedure “spStatProc” inside the deptest database.

Let’s create another stored procedure that contains dynamic SQL which retrieves all the records from the student table.

USE deptest
GO
CREATE PROC spDynProc
AS
BEGIN
	DECLARE @query NVARCHAR(100)
	SET @query = 'SELECT * FROM student'
	EXECUTE sp_execute @query
	
END

This script creates a stored procedure “spDynProc” inside the deptest database. This stored procedure uses a dynamic SQL statement to retrieve all the records from the student table.

Now we have two stored procedures that have a dependency on student table. One of them contains static SQL and the other contains dynamic SQL.

However, if you execute the sp_depends stored procedure and pass it the student table as a parameter, you will see that 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:

nametype
dbo.spStatProcstored procedure

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 large numbers of filters and there are multiple AND and OR clauses between the filters. On the other hand, using dynamic SQL you can dynamically generate WHERE clauses depending upon the type of filters. This makes dynamic SQL the better choices if you want to implement extremely complex logic.

Conclusion

Overall, stored procedure outperforms dynamic SQL in almost all aspects. They are faster, secure, and easy to maintain, and require less network traffic. As a rule of thumb, stored procedures should be used in scenarios where you don’t have to modify your queries and your queries are not very complex. However, if you frequently change table names, column names or the number of parameters in your query, Dynamic SQL is the better choice owing to its simpler implementation strategy.

Useful Links

Ben Richardson

Latest posts by Ben Richardson (see all)