Reducing the number of Stored Procedures for the Application

Approach to reduce number of stored procedure for the application

  • Share

Nowadays, in the years of ORM, barely anyone thinks about stored procedures, but they are still there and accessing and reading data with ADO and SqlDataReader ist still the fastest way.

Stored procedures are validated, parsed and compiled in SQL server, so these steps are not required to be performed versus plaint text query (which is what entity framework generates for accessing data).

Even if you decide for entity framework for access, it is still the the fastest to to use it with stored procedures. Beside there are a lot of legacy projects which were developed before entity framework came out and these needs to be maintained.

Using stored procedures to access data for sure involves at least three procedures for one entity for

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

Usually there are more SELECT stored procedures as for sure you are going to need selecting data by one or multiple parameters. I made a small test with AdventireWorks sample database for DimEmployee table. The following SELECT stored procedures are just some you might need to have to select and filter data by different parameters.

Get all without filter

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
	SELECT * FROM DimEmployee
END
GO
    

Get by primary key

CREATE PROCEDURE GetEmployeeByEmployeeKey
	@EmployeeKey int
AS
BEGIN
	SELECT * FROM DimEmployee WHERE
	EmployeeKey = @EmployeeKey
END
GO
    

Filter by secondary/foreign key

CREATE PROCEDURE GetEmployeeByEmployeeNationalIDAlternateKey
	@EmployeeNationalIDAlternateKey nvarchar(15)
AS
BEGIN
	SELECT * FROM DimEmployee WHERE
	EmployeeNationalIDAlternateKey =  @EmployeeNationalIDAlternateKey
END
GO
    

FIlter by two keys

CREATE PROCEDURE GetEmployee
	@EmployeeKey int,
	@EmployeeNationalIDAlternateKey nvarchar(15)
AS
BEGIN
	SELECT * FROM DimEmployee WHERE
	EmployeeKey = @EmployeeKey AND
	EmployeeNationalIDAlternateKey =  @EmployeeNationalIDAlternateKey
END
GO
    

These are just few select stored procedures. In case you need different filtering, you will have to write more. This also means more code in the application level to invoke each stored procedure from SQL server database. This makes your code in the application layer to grow and makes browsing of database stored procedures in SQL Server Management Studio more difficult.

All in one select procedure

Lightbulb OrangeInstead of this, there is a better approach which considers using of optional parameters for Stored procedure.
It will also allow you to write one method in application layer with multiple overloads or only single methods with optional parameters.

CREATE PROCEDURE GetEmployee
	@EmployeeKey int = NULL,
	@EmployeeNationalIDAlternateKey nvarchar(15) = NULL
BEGIN
	SELECT * FROM DimEmployee WHERE
	EmployeeKey = ISNULL(@EmployeeKey, EmployeeKey) AND
	EmployeeNationalIDAlternateKey =  ISNULL(@EmployeeNationalIDAlternateKey, EmployeeNationalIDAlternateKey)
END
GO
    

After you add optional parameters in stored procedure, you should also handle them in a way to exclude that part of condition in select. This can be done with ISNULL which basically makes comparison in WHERE clause to compare value with it self if the parameter is NULL or to compare to parameter is parameter value is not NULL.

This can significantly reduce both number of stored procedures and methods in the application level layer of application.

  • Share

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.

Comments for this article

comments powered by Disqus