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.
Hide Copy Code
CREATE VIEW [dbo].[v_CardDefinitions] AS
Cards.ID AS CardID,
Users.Email AS UserEmail,
Modules.Name AS ModuleName
Cards ON CardDefinitions.ID = Cards.CardDefinitionID
Modules ON CardDefinitions.ModuleID = Modules.ID
Users ON Cards.UserID = Users.ID
CardDefinitions.Active = 1
Example stored procedure
Hide Copy Code
CREATE PROCEDURE [dbo].[Cards_GetById]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; SELECT
DISTINCT ID, Name, [Permissions]
ID = @cardId
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.