To ensure key business logic remains consistent, it makes sense to run the business logic on the server in stored procedures rather than rely on each version of a program to implement and properly execute the rules. This helps with consistency, as programs execute the same logic. This also means that the quality of the data is better. For example, in the AdventureWorks database, a person can have up to three phone numbers: cell, home, and work.
Suppose whenever a person is added, we need to also add two of these numbers; their home and wok number. How can we easily ensure this? We could rely on each developer to write code to create the insert statement to add both phone numbers.
But if there are many programs and programmers, can we ever hope for them to be consistent? Rather than having to hunt down areas in each application and make changes, you only need to make changes to the stored procedure. They make it easier to support complicated code as they promote modular programming. Keep in mind that they can call each other. This is important, it allows you to take a very complicated process, and break into down into manageable pieces. Ideally each piece performs one job and does it well.
Additionally, by pulling the business rules into the procedures, we centralize the code. Having this logic in one place make it easier to troubleshoot any issues, and chase down inconsistencies.
If you break your problem into too many pieces, it can become too hard to support. It is a tradeoff. You can set up your database security so that applications can only access and change data through stored procedure calls.
Ad-hoc queries or direct access to tables would not be allowed. Since stored procedures are located within the database you can set up security to restrict which users are able see and execute them.
This is handy if you have sensitive queries or code which you want to run in a controlled manner. Not only can you control who can see the stored procedure code, and perhaps sensitive information contained in the queries themselves, but you can control access to who can execute them. Also, by using stored procedures we can avoid SQL injection attacks.
These can be avoided by parameterizing your queries. The SP treats input parameters as literal values and not executable code. This makes it harder for attackers to try and trick your queries into performing unintended actions. Also, in addition the DBMS caches them, so once executed, the queries, are ready for repeated execution. Some of the disadvantages I think are worth mentioning include:.
They are written in vendor specific languages and that makes it hard to transfer them from one installation, such as Oracle, to another like SQL Server. Testing and debugging stored procedures can be tricky. It can be more difficult to put together the debugging tools to allow you to step through and trace them. It is harder to keep track of changes made to stored procedures than it is native source code. I feel that stored procedures have their place.
I think that using stored procedures to insert and change data is a really good idea. I had put together some extensive ad-hoc report writers and needed a way to generate SQL code on the client and present it to the server. For these ad-hoc cases I shy away from stored procedures. We will create a simple stored procedure that joins two tables and returns the result set as shown in the following example.
When we execute the procedure GetProductDesc, the result set looks like below. Let us create a SQL Server stored procedure that accepts the input parameters and processes the records based on the input parameter. While executing the stored procedure we need to pass the input parameter. Please refer to the below image for the result set. When we execute the above procedure without passing the parameter value, the default value will be used.
But when executed passing the value, the default value will be ignored and the passed value will be considered as a parameter. Below is the example of a stored procedure with an output parameter. The following example retrieves the EmpID which is an auto identity column when a new employee is inserted.
Executing the stored procedures with output parameters is bit different. We must declare the variable to store the value returned by the output parameter. When you try to script the encrypted stored procedure from SQL Server management studio, it throws an error as below.
Like the temporary table, we can create temporary procedures as well. Change Data Capture Stored Procedures. Cursor Stored Procedures. Data Collector Stored Procedures. Used to work with the data collector and the following components: collection sets, collection items, and collection types.
Database Engine Stored Procedures. Database Maintenance Plan Stored Procedures. Distributed Queries Stored Procedures. Full-Text Search Stored Procedures. General Extended Stored Procedures. Used to provide an interface from an instance of SQL Server to external programs for various maintenance activities.
Log Shipping Stored Procedures. Policy-Based Management Stored Procedures. PolyBase stored procedures. Replication Stored Procedures.
0コメント