Talk:Stored procedure

Opinion

''From the article:' ':By providing an API that implements business logic within the database using stored procedures, the need to duplicate logic within client programs is lessened or eliminated. [...].

This sounds a bit biased. The opposite is just as true. Duplication can be pushed either to the database or to the client programs, and there are arguably more effective ways to deal with it in the second case. The sentence seems to imply the case where there are many client programs that don't share anything above the database level. In this case the author is right, but this is a special case, not a general truth.

Some critics claim that databases should be for storing data only, and that business logic should only be implemented by writing a business layer of code, through which client applications should access the data. However, the database system is intended to not only store the data but also to ensure its integrity and consistency.

"Some critics" refers to a large portion of the software development field, and probably a vast majority of software development thought leaders. The passage is an opinion and, as far as I know, not a very widely shared one.

Nitpicking:

[...]a business layer of code, through which client applications should access the data.

The author seems to be talking about three-tier architectures. In this approach, the business layer is the core of the application, not the layer through which the application accesses data. The presence of a data layer access (for example an Object-Relational Mapper) as a subset of the business layer is not integral to the pattern - the business logics might access the data through many different means, like Active Records or Embedded SQL.

I won't modify the page right now - I'm waiting for comments.

Nusco 22:21, 15 December 2006 (UTC) 22:18, 15 December 2006 (UTC)

Nusco: I've made changes to this second "Some critics" paragraph. Do you approve? I'm of the opinion that there are considerable advantages to operating a business layer and using SPROCs. Indeed I do this myself. After the modification the paragraph now reads:

Some critics claim that databases should be for storing data only, and that business logic should only be implemented by writing a business layer of code, through which client applications should access the data. However, the use of stored procedures does not preclude the use of a business layer.

Question : The origin of stored procedure, should that not be found in this article - who created the first sp and where did the influence come from? Like 'computer history' — Preceding unsigned comment added by 81.228.153.27 (talk) 12:03, 23 August 2019 (UTC)

Markblue

Adding cleanup template

I'm adding a cleanup template the article because it looks as if it's evolved to be a bit disorganised. I'll have a go at it in the next little while if nobody else gets there first. Izogi 19:10, 7 April 2006 (UTC)

"The advantage of a stored procedure"

From the article:

The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server and is generally faster at processing database requests.

Is this correct? It doesn't seem very clear to me. If I submit some inline SQL to a database engine, it will still execute just as quickly (except for things like compilation), it will execute inside the database engine, and will only return the results that the SQL query requested. I think what the author might have meant is that the result of multiple queries can be strung together inside the database engine, allowing an entire combined operation to be carried out internally without having to ferry intermediate results and further requests back and forth between database and application. Izogi 19:10, 7 April 2006 (UTC)

The previous makes sence, since stored procedures that are frequently used are allready compiled so no further compiling is neaded, on the other side network load is minimised since everything is done on the db server. So I can say that the previous is correct —Preceding unsigned comment added by 92.55.115.168 (talk) 12:03, 15 April 2009 (UTC)

I find it distracting that these 3 paragraphs seem to argue. I will consolidate them into one, non-argumentative paragraph and if anyone else has better ideas, feel free to re-write "Pre-compilation of SQL statements SQL statements implemented as stored procedures may run faster in some circumstances, as they can be pre-compiled. Execution plans for compiled statements can be stored in the database, together with the procedure. That removes the SQL compilation overhead that is typically required in situations where software applications send inline SQL queries to a database.

Pre-compilation implies that the execution plan may not be optimal in all cases. Some information is typically unavailable at the time that the execution plan is generated, for example, the exact values used in predicates. Out-dated statistics are another potential source for suboptimal plans.

The performance benefits of pre-compilation of stored procedures are largely exaggerated. Embedded static SQL is also pre-compiled and, therefore, offers the same advantages without stored procedures. Most database systems implement statement caches to avoid repetitive compilation of dynamic SQL statements. In the case of MS SQL Server, there is no pre-compilation of SQL statements. http://msdn2.microsoft.com/en-us/library/ms190201.aspx" Marr75 20:35, 24 July 2007 (UTC)

Merge from User Defined Function

• Merge and redirect UDF to here since this term is used in template - I'm not an SQL expert but it looks like two terms that are exactly the same. -- RevRagnarok ^{Talk Contrib Reverts} 11:59, 17 June 2006 (UTC)

Stored procedures and UDFs are vastly different, and so they shouldn't be merged. -- Mikeblas 21:39, 17 June 2006 (UTC)

I think your confusion came from a bad descrpition on the UDF page. I've edited that page and removed the merge tags. Let me know if you think it could be clearer or find any errors. I'll also some text to this article about UDFs to highlight the differences. -- Mikeblas 21:57, 17 June 2006 (UTC)

SQL Injection

Correct me if I'm wrong, but stored procedures can't usually be vulnerable to SQL injection, which is something that should be mentioned under 'Security'.83.78.45.52 14:07, 31 August 2007 (UTC) Neither can any ORM I've encountered. Avoiding SQL injection is standard practise, not "a reason to choose SPs". Pro-SP vs anti-SP is quite a lively debate in the software development community, and it's not this article's place to be putting a bias on that. It might be important to touch on all the issues of "what benefits do SPs have", but the major ones to touch on are "what benefits do SPs have that you can't replicate elsewhere" (i.e. in a business layer). 87.246.96.2 (talk) 12:07, 18 June 2008 (UTC)

Example

Might be a good idea to provide an example to illustrate the concept of an SP? As I'm not an expert I'd left it to others. —Preceding unsigned comment added by 192.54.144.229 (talk) 10:14, 28 September 2007 (UTC)

Disadvantages

This article should have a section about the disadvantages of stored procedures (many, as documented by a "why not stored procedure" Google search), and their alternatives (like web services that encapsulate business logic/shield database access in a reusable way).

http://www.google.co.ve/search?q=why+not+stored+procedures

--Juanco 18:51, 28 September 2007 (UTC)

pro-SP vs anti-SP is quite a lively debate in the software development community, and it's not this article's place to put a bias on that. You're right that the disadvantages of an SP aren't clear from the article (and where they are, they're weasly scattered around the advantages). Maybe we should try to rewrite to seperate out "What is an SP?" from "Why would/wouldn't you use an SP?"

Ultimately, though, it's not our job to tell developers what to do. It's only our job to tell someone unfamiliar with the topic what a developer might do. "A full discourse on when and when not to use SPs is beyond the scope of this article, however some well-known advantages and disadvantages include:"

Then list the major non-controversial ones. Thoughts? 87.246.96.2 (talk) 12:20, 18 June 2008 (UTC)

Unreferenced Tag - Pre-Compiled SP

With today's PC hardware, are SPs still pre-compiled? Does anyone have any source to back this up?

Here are some sources that say SP are NOT pre-compiled. Postgre http://c2.com/cgi/wiki?PostgreSql MySQL http://www.pcwelt.de/it-profi/englishnews/Software/128397/

MS SQL Server http://msdn2.microsoft.com/en-us/library/ms190201.aspx (Note: This URL doesn't support your arguement. True SQL stores sp's as text, but when they are 1st executed they are complied & their optimised execution plan is stored in the procedure cache. 2nd & subsequent executions they are retrieved, thus saving the Query parse, Plan generation & Cost based optimisation phases. In frequenely executed queries or those with very complex execution plans, this often results in a significant CPU saving. Especially with modern Web App's that typically have a small set of very frequently executed queries. The benefits are even more pronounces where they use ORM layers like nHibernate or LINQ-to-SQL (as they typically produce very verbose queries which results in excessive network traffic.) Of course they only have a finite life in cache, if they are rarely executed, they eventually get discarded from cache & will need to be parsed, if they ever executed again. )

It seems pre-compiled is a knee-jerk reaction. Ask what are the advantages of SP and first response is pre-compiled but it would seem SP was never pre-compiled in many database systems. Does anyone know which db provide pre-compiled SP from a reliable source (preferrably from the db vendors)? NYCDA (talk) 23:04, 10 December 2007 (UTC)

1.1. What is a Stored Procedure?

A DB2 stored procedure is a special program running under the control of the database within a Work Load Manager application environment on the mainframe. It usually performs a specific business function on behalf of a calling client. By combining several SQL calls into a single module run locally by DB2 for OS/390, significant savings can be made for distributed applications by reducing the network delays associated with individual SQL calls made by the client (see diagram below). Stored procedures can also be called on the mainframe from other Stored Procedures, batch or CICS using EXEC SQL CALL. RBSG’s preference is that you do not call them from CICS. Implementing I/O modules as Stored Procedures is not recommended as it results in multiple SP calls thus negating the performance benefits of reduce network traffic (see section 2 for more details).

The client issues an SQL CALL statement, passing all required input parameters to the stored procedure. After execution, the stored procedure will set any defined output parameters before returning control back to the calling application. It is also possible for the stored procedure to pass back entire result sets via DB2's cursor mechanism, which doesn’t require output parameters for the result set to be defined.

Stored Procedures can be written in various languages. In reality this means COBOL for RBSG. The programs look just like batch subroutines – they have PARMs, and can use most normal COBOL verbs. However, because they are executed in dynamically started address spaces, with standard JCL, you cannot in practice use VSAM or QSAM files, as file allocate/deallocation is complex. SPs, then, must only access DB2 data access.

1.2. Necessary components of a Stored Procedure

A Stored Procedure is the server in a Client/Server interchange. The Client will know the SP by a DB2 name, which can be up to 18 characters. However, as a Load Module in a program library, the SP is restricted to an eight-byte name.

All the information needed to enable DB2 to handle these things is stored in the DB2 Catalog, and we pass it in using a small file called an SPDEF (Stored Procedure Definition). You need to build one of these for every Stored Procedure you code. SPDEF is a recognised type of object in Endevor.

The SP program itself has to be held in the libraries available to the Work Load Managed SP address spaces. This is achieved by using type COBP for the program in Endevor. This, together with the SPDEF and a dummy BIND member, constitute the components of a single SP.

1.3. Example types of SP

At the most basic level, we can define two types of Stored Procedure.

1. Single row retrieval, data entry, and data update. 2. Data retrieval based on a cursor.

These are structured and behave in different ways, particularly in the off-host environment, where Java constructs provide a lot of functionality for result set handling. —Preceding unsigned comment added by 220.227.179.4 (talk) 05:30, 6 May 2008 (UTC) Do you know in which format store Procedure is stored if you know please share it thanks Umair Raja