Why do we need a stored procedure?

Why do we need a stored procedure?

A stored procedure provides an important layer of security between the user interface and the database. It supports security through data access controls because end users may enter or change data, but do not write procedures. It improves productivity because statements in a stored procedure only must be written once.

Which object is implemented DB logic?

Table – This database object is used to create a table in database. View – This database object is used to create a view in database. A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed.

Why are business logic in stored procedures difficult?

Number two is that you are tying your business logic to your persistence mechanism. You may not store all of your data in the same database, or some may reside in XML etc. This type of inconsistency is difficult on the developer. Validation is difficult to perform if the logic resides only in the database.

Can a stored procedure be shared across different applications?

Stored Procedures can be shared across different applications. Our team have a soft rule here. Sometimes it’s better to solve the Business Logic in T-SQL, sometimes it’s easier to do it in c# (Business Layer). So we have a pragmatic solution: Put where it fits better.

What are the pros and cons of storing procedures in a database?

Pros of holding all the business logic on stored procedures in web application for: One place is easier to maintain than multiple. Databases are fast and optimized and can be scaled well. The same procedures can be called from mutiple, different frameworks and languages.

Which is better compiled code or stored procedures?

Compiled code is more stable and easier to work with. If I’m debugging something there is so much information available if I used C# or Java. Stored procedures are likely to end up with too many responsibilities, it’s a best practice to try to keep your responsibilities to 1.