Image of Beginner's guide to creating a SQL Server stored procedure

ADVERTISEMENT

Table of Contents

Introduction

Stored procedures are a sequences of queries and commands 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

  1. Keywords - always uppercase to increase readability.
  2. Variables - use as few variables as possible to free up space in the cache.
  3. 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.
  4. SELECT vs SET - the SELECT statement is better at assigning values to multiple variables and is much faster than using lots of SET statements to assign values to your variables.
  5. Fully qualified names - always use the fully qualified name when calling stored procedures, like EXEC master.dbo.Your_Proc_name
  6. 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.
  7. Avoid DISTINCT and ORDER BY - if you don't strictly need the DISTINCT or ORDER BY clause, avoid using it. Unnecessary use of these clauses causes extra load on the database engine.
  8. Avoid using cursors - using cursors makes the execution slower as it runs against SET based SQL.
  9. Use neat indentation - this significantly enhances the experience when developing out the stored procedure in the future.
  10. Write thoughtful comments - yourself and other developers might come back and re-read the code, and think... WTF does that do!

Support for stored procedures

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.

Maintenance of stored procedures

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 of stored procedures

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, using grants:

GRANT EXECUTE ON dbo.procname TO username;

Conclusion

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.

Final Notes