A certain amount of this is almost inevitable in modern-day apps in the form of foreign keys and triggers, but occasionally, major editing and validation may be warranted as well.
You need performance that can only be achieved by running logic on the database server itself and not as a client. So it behooves you to ensure that if there are significant bits of the offending operation that CAN be offloaded onto clients, you can separate them out and leave the most critical stuff for the DBMS server. Another scenario would involve use cases where it does some protocol when handling the tables hint: defined steps which transactions are likely to be involved , this could benefit from locality of reference: Being in the server, queries might benefit.
OTOH, you could supply a batch of statements directly into the server. Specially when you're on a XA environment and you have to access federated databases. If you are talking business logic rather than just "Should I use sprocs in general" I would say you should put business logic in sprocs when you are carrying out large set based operations or any other time executing the logic would require a large number of calls to the db from the app.
It also depends on your audience. Is ease of installation and portability across DBMSs important to you? If your program should be easy to install and easy to run on different database systems then you should stay away from stored procedures and also look out for non-portable SQL in your code. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow.
Learn more. When should I use stored procedures? Ask Question. Asked 12 years, 3 months ago. Active 4 months ago. Viewed 26k times. Are there any rules of thumb that you can think of in reference to this? IAdapter Suggest removing the php tag as php could easily be any other programming language. This very extensive answer may better answer your question then the answers that are provided here.
Add a comment. Active Oldest Votes. Just wanted to restate that. The SQL goes in a stored procedure, the procedure is called from code. No code ever touches even so much as a select statement.
Running an SP or not is the difference between pre-compiled and interpreted code- which one do you prefer? The "well-documented" performance item is actually a non-issue depending on the database engine you are using. When using procs you can deny direct table access and thereby completely secure yourself against most forms of destructive sql injection. Otherwise you are dependent on the code to stop it; which not all programmers are created equal.
Chris Lively: Parameterized queries are the most secure against sql injections. See palisade. KB: first, sql injection is just one attack vector against databases. Second, parameterized queries will not save you from someone uploading a malicious document with embedded code. Nor will it save you if a developer forgets to do it.
However, simply not allowing direct table access in the first place will prevent all of that. And you make it impossible for code to select the data it wants. I cannot find value on SP for everything. Show 3 more comments. With a corporate database the asset is valuable and invalid data or actions can have business-threatening consequences.
Your primary concern is safeguarding the business, not how convenient access is for your coders. Such databases are by definition accessed by more than one application.
You need to use the abstraction that stored procedures offer so the database can be changed when application A is upgraded and you don't have the resource to upgrade application B. Similarly the encapsulation of business logic in SPs rather than in application code allows changes to such logic to be implemented across the business more easily and reliably than if such logic is embedded in application code.
For example if a tax calculation changes it's less work, and more robust, if the calculation has to be changed in one SP than multiple applications. The rule of thumb here is that the business rule should be implemented at the closest point to the data where it is unique - so if you have a specialist application then the logic for that app can be implemented in that app, but logic more widely applicable to the business should be implemented in SPs.
Cruachan Cruachan Bad examples do not eliminate the advantages when a method is used properly. An example is always limited and never proves a generalization. That's ridiculous. I'm not advocating splitting business logic between the middle tier and the data layer- but your middle tier should be using SPs exclusively to deal with the data.
It's a performance thing, unless you're using some crappy rdbms that doesn't precompile store procedures. I'd like to hear one single "Serious Downside". Could not disagree more.. Oraganizing, and by doing so improving the maintainability, of Database Access code is not a Gain?
Just like once the performance gain of assembly language was important, now it's irrelevant for most uses. Once the table is created, open table in your SSMS and add some data by manually entering data to the table.
See the Select statement in the below code. From here, you can also modify an exisitng SP. Alternatively, you can also execute a SP from the Query window. Parameters in SPs are used to pass input values and return output values. There are two types of parameters: Input parameters - Pass values to a stored procedure. Output parameters - Return values from a stored procedure. In the previous steps, we created a simple SP that returned all rows from a table. Now, let's create a new SP that will take a city name as an inpurt parameter and will return all rows where city name matches the input parameter value.
Here is the updated SP with a parameter CityName. Pass the value of parameter in Execute dialog box. You can also run the same SP in code. The ID is passed as an input parameter. WHERE command. You will see the SP is created.
Now, Right click on SP name and select Execute stored procedure…. Report Error. Your message has been sent to W3Schools. W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. 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.
0コメント