Improving Your SQL Stored Procedures

Dominic Burford

May 2, 2019·2 min read

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.