Talk:User-defined function

Expand for other uses?

SQL is not the only programming language that uses user defined functions. For example ColdFusion also uses them. I’m not sure how many other languages use this term however. Should this article be expanded to include other uses of user defined functions? —The preceding unsigned comment was added by Rwblackburn (talk • contribs) 13:50, April 15, 2005 UTC.

Yes... it should :) Unfortunately I don't know enough to do it without some research. —Seqsea (talk) 00:44, 16 April 2006 (UTC)

Obviously, almost any useful programming language lets you re-use your own code. I don't think that is the point of UDF. (Note: I am an Electrical Engineer who can program, not a Computer Science major, so I may be wrong here!) I believe the point is that SQL - Q is QUERY. So if you can get the SERVER to do something for you that is not a query, that's a big deal. The computation is offloaded from the client. I think that this diff should be reverted because if not it is going to explode into a Hello World page showing how to define a function in every possible language, drowning out the uniqueness of the SQL implementation... -- RevRagnarok ^{Talk Contrib Reverts} 17:26, 16 June 2006 (UTC)

The term "User Defined Function" is rare in modern programming, because it describes almost all code that is written, so there's no need to turn this into an enormous list. I mentioned BASIC because there's a language feature that is generally referred to as "user defined function". You don't talk about UDF in C or Lisp or any modern language, it's assumed. I take the point about the significance client side vs server side processing, but I don't think the phrase "user defined function" really captures it; after all, in SQL all functions (such as arithmetic or string functions) are evaluated on the server. I do agree that there would be no benefit in listing a lo tof languages. Amcguinn 19:21, 16 June 2006 (UTC)

In fact, I think only ColdFusion and MySQL use this term. Google turns up some uses with Microsoft SQL Server, but only third-party stuff; no official production documentation. This is an uncommon name for a ubiquitous thing. -Slamb 00:06, 19 September 2006 (UTC)

How about this: move the BASIC section below the SQL section, and rename it as "other uses of the term" with something along the lines of "'User defined function' in some older programming languages was used to describe a function written by the programmer rather than included with the language - such usage has become obsolete since in structured programming virtually all program code consists of user defined functions" - would that be better? Amcguinn 19:30, 16 June 2006 (UTC)

I think that's a great suggestion. Maybe even massage what I wrote above a little as to why it's a big deal in these two cases...? I'd work on it, but I am out of town for a week starting in the morn... -- RevRagnarok ^{Talk Contrib Reverts} 03:43, 17 June 2006 (UTC)

Stored procedure

I'd been racking my brains to remember what I used to refer to database user-defined functions as, and I remembered "stored procedure". There's already an article there.

The options are:

• Make this page a redirect to Stored procedure
• Make it a brief account of user defined functions in different contexts, including a link to stored procedure
• Delete this altogether.

- Amcguinn 10:18, 17 June 2006 (UTC)

• My response. ;) Then redirect. -- RevRagnarok ^{Talk Contrib Reverts} 11:59, 17 June 2006 (UTC)

My suggestion: Merge the BASIC portion with the main BASIC article. Merge the database portion into Stored Procedure. Then delete. -- PabSungenis 14:22, 17 June 2006 (UTC)

BASIC#Procedures_and_flow_control already refers to the DEF FN syntax - I don't think anything here adds to that. It would only really be useful here as an answer to "What does 'User defined function' mean", but that is possibly too dictionary-like for WP. Amcguinn 15:28, 17 June 2006 (UTC)

Stored procedures are very different than user-defined functions. This article was incorrect to say that UDFs are stored procs. I've fixed it up and removed the merge request. -- Mikeblas 21:52, 17 June 2006 (UTC)

Title

Why isn't the title of this topic "User-defined function"? -- Mikeblas 14:52, 18 June 2006 (UTC)

I agree. I'm not a native English speaker, but it only makes sense to use the tightly coupled wording. --Stolze 18:22, 17 August 2006 (UTC)

SQL Server 2000

Why is there a section specific to SQL Server 2000, when the concept of a user-defined function isn't specific to the SQL Server database manager? Every database manager I can think of -- Oracle, DB2, Postgres, MYSql, etc -- all have this concept. —Preceding unsigned comment added by 199.246.40.54 (talk) 19:26, 15 October 2008 (UTC)

Minor corrections

The samples should use standardized SQL and not some product-specific implementation. For example, the SQL standard does not use the '@' for variable names.

The statement that stored procedures cannot be evaluated on a per-row basis is not correct. First, it is possible to call stored procedures from a trigger and triggers can be declared as FOR EACH ROW. Thus, the body of a trigger is evaluated once-per-row, implying that a procedure call in the trigger body is invoked once per row. Next, some products (like DB2) allow that procedures can be called from within a function. Again, the procedure is invoked on a per-row basis.

The fact and concepts of table functions should be added.

The statement that stored procedures return a "set of rows" is too restrictive. A stored procedure may return result sets (multiple!), but it doesn't have to.—Preceding unsigned comment added by 141.35.14.130 (talk • contribs)

Be bold! This is not something I know a lot about, feel free to clean it up! — RevRagnarok ^{Talk Contrib} 20:11, 28 July 2006 (UTC)

As far as I can tell, the SQL standard further does not include user defined functions--so variable syntax doesn't seem relevant. You might want to add another example or a description to the article explaining this.

The point the article is trying to make is that a function is invoked within a statement once per row within the statement. A stored procedure can't be evaluated once per row within a statement -- unless you wrap it in another function. (Note that the body of a triger, declared FOR EACH ROW or not, is semantically equivalent to a function.) If you can find a more accurate way to express that, let us know! -- Mikeblas 20:24, 28 July 2006 (UTC)

Hey, I found it -- I forgot that ANSI SQL uses "CALL". The way Melton and Simon explain it (in ISBN 1-55860-456-1) is that procedures are involved exclusive by means of a specific SQL statment: the CALL statement; by contrast, SQL-invoked functions are treated as "values" in an expression of some sort and are invoked exclusively as a means of evaluating that value. You can't use CALL for per-row evaluation within DQL. -- Mikeblas 20:32, 28 July 2006 (UTC)

Actually, SQL:2003 (the current version of the SQL standard) supports UDFs and stored procedures. It does that since SQL3. See subclause 11.50, "<SQL-invoked routine>". I understand your point regarding "once per row". But I think it is not correctly expressed. Besides, there are table functions (also in the standard) and those have a slightly different behavior when invoked. (I'll try to incorporate my thoughts later on.) -- stolze 08:30, 31 July 2006 (UTC)