Re: How to use a stored procedure in a select structure?

[Roland Bouman]

Ok, but there's still the possiblity that a procedure might return multiple resultsets. On the other hand, if the machinery is intelligent enough, it can foresee that too, so....

Anyway, I tend to regard the distinction between procedures and functions as a bit superficial - I don't mean there's no difference, I mean that a function is just a particular case of a procedure (not neccessarily in the mysql implementation, I mean in general).

I always used to think that the distinction had to do with the ability to return a value - functions do, and procedures dont. Well, I have had to leave that assumption quite a while ago: C and java functions can be void, which is maybe not entirely the same as not returning a value - it sure comes close. And, from a couple of posts I read at this forum, I learnt that a MS SQL procedure can return a value (anyone to confirm that?). Bye Bye criterion1

Then, there are all those functional languages such as LISP, but also XSLT that do not allow a function to have side effects. THis makes sense to me, having one particular construct that says: hey, I'm a genuine mathematical calculation and I do not alter my environment - come here and use that knowledge to optimize the code i'm sitting in. Well, all database products I know allow your function to perform sidefx, so that´s bye bye criterion two.

We could say that a procedure is a kind of thing that does not return a resultset (becoming less convincing i think): to me, that's merely an issue of datatypes. If you have something like Oracle collection types, or MSSQL table datatype or indeed a resultset objecttype there's no objection to a function returning a bunch of records.

And, I said before, I still do not grasp this manner of returning a resultset from a procedure like in MySQL procs, but also in MS SQL (anyone to confirm that?). Wouldnt it be much cleaner to use a variable for that, so that you can look at the declaration and see: Ah, well, here's a proction (well...) that returns a resultset or two?

Anyone on this?



Edited 1 time(s). Last edit at 11/16/2005 01:25PM by Roland Bouman.