Rusty Razor Blade
I do not think you can use the result of a stored procedure in an ad-hoc subquery.
On my social network, LetsGetNuts.com, I have a Friend table. This is the structure:
mysql> describe Friend;
+—————-+—————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————-+—————+——+—–+———+——-+
| fkUser | int(11) | YES | MUL | NULL | |
| fkFriend | int(11) | YES | MUL | NULL | |
| confirmed | enum(‘Y’,'N’) | YES | MUL | NULL | |
| confirmed_date | date | YES | | NULL | |
+—————-+—————+——+—–+———+——-+
4 rows in set (0.02 sec)
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 was hoping I could write a stored proc with a union that would record a recordset of userids that I could use in a subquery, kind of like:
SELECT username, firstname, lastname from users where userid in (friendslist());
No such luck, it looks like you can’t do it. I’ve tried a few variations, including call friendslist(), etc… none of them work.
If it’s possible to do this in MySQL I’d be thrilled to know how.
For reference, it’s actually better for me (I think) to just run the stored proc, and saved the comma delimited list on the web server side, instead of running the query against the database several times per page. However, I still would like to know if it’s possible to do the above.
If you enjoyed this article, please consider sharing it!
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…
Hi Ron,
Here’s why there’s a union…
If User1 asks to friend User2, the record would be (User1, User). It’s the only record in the db.
If User 2 asks to friend User1, the record is (User2, User1).
So, the friend list is a union of
(select fkFriend from Friend
Where fkUser = 1 )
union
(select fkUser from Friend where
fkFriend = 1)
In closing, it’s not really the example that I was trying to discuss. It was only meant to be a practical use of a stored procedure within a subquery.
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….
Hi Roland, (not Ron, sorry)
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. It’s completely possible that I might figure out a better way of getting the friends list later on – I’d have to change it in 50 places again.
Once again, it’s not really about the example I used, but the concept of stored procs within a subquery.
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.
Hi again Roland,
Lets assume that my procedure was more complex, say if it had done several queries, used some temp tables, and a few cursors. 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 and suggest a workaround as “better” while dismissing the value of this missing feature.
I don’t know if Oracle or MSSQL supports this either, so it may be something that no one has.
Roland,
I didn’t think it could be done with a view at first because of my table structure. I just got it figured out though by unioning the table to a reversed version of itself.
create view friendlist as
(SELECT f1.fkFriend as user1, f1.fkUser as user2 from Friend f1 WHERE f1.fkFriend <> f1.fkUser and confirmed = ‘Y’)
union
(SELECT f2.fkUser as user1, f2.fkFriend as user2 from Friend f2 WHERE f2.fkFriend <> f2.fkUser and confirmed = ‘Y’)
Oh yeah.
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.
“Alas…I just lost my comment – invalid security code and the back button (ff) does not work.”
Hmm.. bummer. I’m using a WordPress plugin for it, maybe I’ll have it check w/ ajax instead.
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.
Tyler: Exactly what I was getting at. I’m glad that this is suppored in mssql, hopefully it’ll get added to MySQL as well.
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.
Visual Studio and SQL Server management Studio are two different entities. So it takes a lot of time when we try an interaction with the database from Visual Studio then it takes a lot of time.
When we are using desktop applications then we do not realize this time delay, but while using web applications the time delay becomes an important factor because the database is on the remote server so a huge time delay is observed.
Normally, the query that we write in Visual Studio is first compiled and then executed in Sql Server which then returns the results. So, this is why a time delay is observed.
The answer to this is Stored Procedure usage. The Stored Procedure is stored in compiled format in SQL server so when we call it from Visual Studio application then it is just executed. Thus saving a lot of time.
An illustration of proper usage of STORED PROCEDURE has been displayed in the following video. You may see it:
http://www.visiontechno.net/studymats/storeprocedure.html
I’m having sort of the same issue as is outlined here to be honest. I have a core stored procedure that is quite changeable functionally and as such am linking to this from several other sources as a definitive point of information.
Initially I came up with the idea of emulating the subquery fnuctionality with a comma seperated list of id’s which are then parsed in a loop calling the SP every time, but this is rather slow.
For some reason it hasn’t occured to me before to package this in a function as mentioned earlier by Roland, but maybe I’ll look at that tomorrow.
Good post!