Stored procedures are a sequences of queries written in a PL/SQL language. They are stored queries which are frequently applied to your model, to share the processing load with the application layer. If you have an SQL query that you execute repeatedly, then consider using a stored procedure to automate your process. It’s possible to pass input parameters into stored procedures, and even pipe output to other stored procedure.
Stored procedures are created by using the
CREATE PROCEDURE keyword.
Here is a simple example, showing the stored procedure:
CREATE PROCEDURE SelectAllLandmarks @City nvarchar(30) AS SELECT * FROM Landmarks WHERE City = @City GO;
Executing the stored procedure:
EXEC SelectAllLandmarks @City = "San Francisco";
So when the stored procedure is called by
EXEC, this runs the stored procedure with the parameter
@City being “San Francisco”. This will output all the landmarks from around the city of San Francisco!
This can be run again, and again and again…
A huge upside of the stored proc is that it provides centralization, such that the data access logic can be assembled all-in-one spot. This allows the DBAs to easily access the stored procedure, spending less effort writing query optimizations.
Here are 10 tips when writing stored procedures:
Keywords - always uppercase to increase readibility.
Variables - use as few variables as possible to free up space in the cache.
Minimize - minimize the usage of dynamic queries - there will be less recompilation of the execution plan AND you will have less worry about SQL injection.
SELECT vs SET - the SELECT statement is better at assigning values to multiple variables and is much faster than using lots SET statements to assign values to your variables.
Fully qualified names - always use the fully qualified name when calling stored procedures.
EXEC master.dbo.Your_Proc_namegood ✔️
EXEC Your_Proc_namebad ❌
SET NOCOUNT ON - this removes the message showing ‘number of rows affected’ by SQL statement. This can actually affect performance if the procedure is called frequently.
Avoid DISTINCT and ORDER BY - if you don’t strictly need the DISTINCT/ORDER BY clause, avoid using it. Unnecessary DISTINCT and ORDER BY clauses causes extra load on the database engine.
Avoid using cursors - using cursors makes the execution slower as it runs against SET based SQL.
Use neat indentation - this significantly enhances the experience when developing out the stored procedure in the future.
Write thoughtful comments - yourself and other developers might come back and re-read the code, and think… WTF does that do.. bad ❌
Most major DBMS’ support stored procedures, however, not all do. You will need to verify with your particular DBMS’ that it provides support for stored procedures. The top 3 DBMS all support stored procedures - Oracle, MySQL, and SQL Server.
There is also a certain level of maintenance associated with your basic CRUD operation when using Stored procedures. For example, for every table you have an Insert, Update, Delete and at least one select based on the primary key, then for each table will require 4 procedures. If you have a large database (1000 tables), you will actually need 4000 procedures! If you have duplicates, that’s even more!
Security is a key benefit of the Stored procedure. Stored procedures help to mitigate SQL Injection, a common application layer attack that may destroy your database. Stored procs provide a layer of security reinforcement by using prepared statements. Additionally, access control can be applied to stored procedures to allow certain users access to certain tables.
GRANT EXECUTE ON dbo.procname TO username;
SQL is an in-demand skill at the moment, and according to PayScale, the average SQL developer salary in the United States is $73,018 per year (at the time of the publication). The median hourly rate, on the other hand, equals $36.42. Why wait to improve your SQL skills?
Here is a great template you can follow to create your own production-grade stored procedures. It provides built-in error handling when dealing with transactions. It also allows the proc to commit or rollback the transaction, which is essential in the development of stored procedures.
Thanks for reading!