Should business logic be in stored procedures?

Should business logic be in stored procedures?

“The default stance in designing an application should be that business logic is held in the application code, NOT in database stored procedures. Only move business logic into StoredProcedures as performance needs required. “

Can we write business logic in stored procedure?

T-SQL Stored Procedures (SPs), along with some views and functions, are a useful way to encapsulate and implement most of an application’s business logic, especially that which retrieves the underlying data from the tables (master or transaction), and/or updates it.

Why you shouldn’t use stored procedures?

Stored procedures are inflexible. Stored procedures are difficult to unit test. With an ORM, you can mock your database code so as to be able to test your business logic quickly. With stored procedures, you have to rebuild an entire test database from scratch.

Why is important to add business logic in stored procedures in a relational database?

Since Stored Procedure are compiled, if you have a complex logic to query the data, it’s better to keep that on the database itself. Also, it will only send the final data to your program at the end.

Should I use stored procedures or not?

The only reason to use a stored procedure, IMHO, is when you must make a complex, multi-staged query that pulls from multiple collated sources. SPs should not contain low-level decision logic, and they should never simply encapsulate an otherwise simple query. There are no benefits and only many drawbacks.

Is ORM better than stored procedure?

The bottom line is stored procedures using a DBMS’s native SQL language can be orders of magnitude faster than ANY ORM generated SQL. However ORM approach is more maintainable. Most ORM devs are not experts at advanced SQL, and have no understanding/experience of this.

Where is business logic stored?

3 Answers. The business logic should be placed in the model, and we should be aiming for fat models and skinny controllers. As a start point, we should start from the controller logic. For example: on update, your controller should direct your code to the method/service that delivers your changes to the model.

What is business logic in SQL?

The term “business logic” in this topic refers to any custom rules or validation tests that you apply to data before it is inserted, updated or deleted from the database.

Are stored procedures safer?

Although SQL Server’s stored procedures help with code security by hiding the implementation of the business logic and even protecting against some kinds of SQL injection attacks — primarily those that use an operator such as AND or OR to append commands onto a valid input parameter value, simply wrapping the code into …

What is not advantage of stored procedure?

Version control is not supported by the stored procedure….Portability –

Advantages	Disadvantages
It is faster.	It is difficult to debug.
It is pre-compiled.	Need expert developer, since difficult to write code.
It reduces network traffic.	It is database dependent.
It is reusable.	It is non-portable.

Is a stored procedure an API?

Stored procedures are the only construct available in SQL Server that can provide the type of interfaces necessary for a comprehensive data API. So, in short, I believe that all data access should be via a fully-defined API, implemented using stored procedures.