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

[Jay Pipes]

Roland Bouman wrote:
> I learnt that a MS SQL procedure can return
> a value (anyone to confirm that?). Bye Bye
> criterion1

Yes, that is true.

> 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?

I agree that lines are blurred regarding functions, stored routines, and views across most major database vendors. The ability in SQL Server to do:

INSERT INTO some_table
EXECUTE some_proc()

is quite useful, though. But, come to think of it, you could just as easily use a View and pass in some WHERE conditions. But, stored procedures allow you to do much more than that in SQL Server. For instance, you could log something to a table and *then* return the resultset to be inserted into the table; something you can't do with a view...

My guess is that as more functionality (pun intended) gets added to each of these features, the lines will become even greyer. We'll have to wait and see...

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com