Rusty Razor Blade

Hi!

“If it’s possible to do this in MySQL I’d be thrilled to know how.”

It’s not. Period.

However:

“The issue here is that building a list of the friends of a certain user involves using a UNION, and I frequently need to use the concept of IN (FRIENDSLIST).”

I don’t see how the UNION is necessay…I mean, could it not be something like this:

SELECT u.username, u.firstname, u.lastname
FROM users u
JOIN friend f
ON u.userid = f.fkFriend
WHERE f.fkUser =

Of course, it becomes interesting when you need to also get the friends of the friends, and the friends of the friends of the friends, etc.

Interesting meaning…impossible, at least with a simple query.

But before I run up that alley…

By Roland Bouman on Sep 27, 2006

Hi jon,

(It’s Roland BTW, not Ron

Right, Ok. I see why you need the union now.

I don’t see why you need the procedure though. I mean, if you can write the union, you can also join agains that union:

SELECT u.username, u.firstname, u.lastname
FROM users u
JOIN (
SELECT fkFriend
FROM Friend
WHERE f.fkUser = [your user with friends]
UNION ALL
SELECT fkUser
FROM Friend
WHERE f.fkFriend = [your user with friends]
) f
ON u.userid = f.fkFriend

And, if you really, really don’t want to join but want to use IN, well, you can do that equally well:

SELECT u.username, u.firstname, u.lastname
FROM users u
WHERE u.userid = (
SELECT fkFriend
FROM Friend
WHERE f.fkUser = [your user with friends]
UNION ALL
SELECT fkUser
FROM Friend
WHERE f.fkFriend = [your user with friends]
)

So, I don’t really see the need for the stored proc yet….

By Roland Bouman on Sep 27, 2006

Hi Jon,

“The reason why I wanted to do it as a stored proc is because it might be done in about 50 places throughout the site. I won’t like it if I have to modify things in more than 1 place when I change something later.”

Views are actually meant to solve that.

“Once again, it’s not really about the example I used, but the concept of stored procs within a subquery.”

Well, I don’t see the ‘concept’. It’s just syntax.

I can see that you want and need both reusability and encapsulation, if that’s the concept you mean, then I agree. But I don’t see why you’d want to have a procedure if you could also use a view for this. I mean, to me, it’s like blaming a car that it doesn’t double as a motorboat.

The fact that a procedure can return a resultset does not mean that it is ‘just like’ a resultset: a procedure can return no resultset, one, or even multiple resultsets; a sp can even do all of these, depending on, say, the parameters or the time of day. So, IMO, it’s not comparable to a subquery or table, and therefore, I don’t see why you should be able to use it as such. At least not when there is a perfectly natural alternative such as a view.

On the other hand, maybe I am missing the point, then please explain what the view cannot do that the procedure can in this particular case.

Thinking about this some more: you can actually something like the trick with the list, but I don’t recommend it. It goes like this:

create a function, not a proc, that returns the list of friends:

create function friendslist(
uid int
) returns text
return (select group_concat(fkFriend)
from (
(
SELECT fkFriend
FROM Friend
WHERE f.fkUser = [your user with friends]
UNION ALL
SELECT fkUser
FROM Friend
WHERE f.fkFriend = [your user with friends]
) f
));

Now, you can use find_in_set:

SELECT u.username, u.firstname, u.lastname
FROM users u
WHERE find_in_set(
u.userid
, friendslist([user with friends])
)

If you don’t think this is a solution, then this would at least implement your original solution

“and saved the comma delimited list”

in the database (where it belongs – it is data centered) instead of on the webserver side.

By Roland Bouman on Sep 27, 2006

Jon,

Nice going on the view!

Tip: use a UNION ALL instead of just UNION. By definition, the two sets do not contain duplicates, and UNION ALL will probably perform better than UNION.

Alas…I just lost my comment – invalid security code and the back button (ff) does not work.

Anyway, to sum it up real short:

“Lets assume that my procedure was more complex, say if it had done several queries, used some temp tables, and a few cursors.”

Yes I know it is thinkable, but it is not very likely. I hope I can argue why: you are querying for data – a list of friends. That’s just what it is – data.

Procedures are there to encapsulate work – that is, sequences of different statements, typically with side-effects (update table a, check input, delete from table b, parse parameter, import data in table c, but not if..etc. etc.)

It seems very strange to me that each time that you are just looking up some data all these changes should

“I can’t really tell if you’re disagreeing with me because you don’t see the point, or if you are trying to defend the database…”

I see the point, but I disagree. I’m not defending MySQL – it is more a matter of programming languages in general. Different kinds of statements do different things – that’s what makes it a good language. Apparent likeness in an aspect of the behaviour of certain statements does not mean you should be able to use them interchangely.

“…and suggest a workaround as “better” while dismissing the value of this missing feature.”

Views are not a workaround – they are there to encapsulate and reuse *query* logic. Procedures are there to encapsulate and reuse *processing* logic. Views normally (ideally) don’t have side-effect. It is almost always an error if a view does have side effects.

“I don’t know if Oracle or MSSQL supports this either”

Oracle does not, MS SQL does. I still have to see a convincing real world example there, too.

kind regards,

Roland.

By Roland Bouman on Sep 27, 2006

What you are describing here is something like

SELECT * FROM TableCreationProc(vars)

correct? msssql 2k+ has this feature, with the UDF retuning tables. A very useful and helpful feature to be sure.

I just came across your site, and thought I give one of those real world examples. The biggest use I’ve found for it is a recursive select on a tree structure. It requires a temporary table, and a loop to be able to get to an undetermined amount of levels, so in this instance a view would be unable to work.

By Tyler MacLeod on Nov 11, 2006

Using procs in subqueries is SOP in SQL Server / Oracle and taken for granted and very useful.

Think of procs as returning a dataset – e.g. same as a query. So any place you would use a query (i.e. SELECT) you should be able to fit a procedure.

For example – “select x from y where g in (select gs from temp)” and “select x from y where g in ( call ret_gs() )” are typical exmples.

Another useful place is in joins:
select x from user u inner join ( call get_groups() ) g on u.id = g.userid.

Its also important to keep the distinction clear between functions and procs. Functions are ment for as a term in a select – e.g. select fun1(),fun2() from X. Functions do NOT return datasets – they return single values. In SQL Server / Oracle you are not allowed to do things like INSERT or CREATE TABLE in functions because of the horribly performance implications of doing insert/create in a function used as a term of a select.

So don’t mind me, just killing a little time after a long day of trying to get newbies to understand how to use procs as an excellent addition to the development toolkit.

By Ken on Mar 12, 2008