Improving Your SQL Stored Procedures

There’s an approach that I have been using for several years now that has helped me improve and simplify my stored procedures. This is for stored procedures that return data i.e. SELECT stored procedures as opposed to INSERT or UPDATE stored procedures. This approach is particularly useful where a stored procedure needs to reference more than one table i.e. where there is a JOINbetween one or more tables.

Firstly I create a VIEW of the data that I want to query. The VIEW contains all the tables, columns, JOINs etc as necessary. It is from this VIEW that the stored procedure will SELECT its data as necessary. All the stored procedure needs to do then is filter the data from the VIEW with a WHERE clause.

The advantages of this approach is that the VIEW hides the underlying details of all the JOINs. The stored procedures then become simple affairs as they simply SELECT from the VIEW. This leads to simpler stored procedures, and allows a VIEW to be reused across multiple stored procedures. Therefore you don’t need to repeat the same complicated JOINs in each of your stored procedures.

Example VIEW

Hide Copy Code

CREATE VIEW [dbo].[v_CardDefinitions] AS
SELECT
CardDefinitions.*,
Cards.ID AS CardID,
Cards.ParentID,
Cards.[Index],
Cards.UserID,
Cards.CardDefinitionID,
Users.Email AS UserEmail,
Modules.Name AS ModuleName
FROM
CardDefinitions
LEFT JOIN
Cards ON CardDefinitions.ID = Cards.CardDefinitionID
JOIN
Modules ON CardDefinitions.ModuleID = Modules.ID
LEFT JOIN
Users ON Cards.UserID = Users.ID
WHERE
CardDefinitions.Active = 1

Example stored procedure

Hide Copy Code

CREATE PROCEDURE [dbo].[Cards_GetById] 
@cardId INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT
DISTINCT ID, Name, [Permissions]
FROM
v_CardDefinitions
WHERE
ID = @cardId
END

So to summarise the approach.

- Create a VIEW of the data that JOINs all the necessary tables
- Create a stored procedure that SELECTs data from the VIEW by filtering the VIEW using WHERE clauses

This is an approach that I use regularly as it simplifies the stored procedures I need to create.

A father, cyclist, vegetarian, atheist, geek and multiple award winning technical author. Loves real ale, fine wine and good music. All round decent chap.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store