Reducing the number of Stored Procedures for the Application
Approach to reduce number of stored procedure for the application
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
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
Instead 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.
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.