Multiple SQL Queries in a Stored Procedure.
Working on a Thursday afternoon, I came across a problem where we had to return multiple counts (about 10 of them ) in an application. One way was to have multiple stored procedures and call them one by one, but this would mean I would have to make 10 connections to the database and 10 opens , 10 closes, phew !
So I decided to combine all of them into one:
SELECT "totaltrees" =(SELECT count(*) FROM Forest f WHERE f.trees= @forestId), "totalplants" =(SELECT count(*) FROM Forest f WHERE f.plants= @forestId), "totalUsers"=(SELECT count(*) FROM Users u WHERE u.groupId= @groupId)
Here you can add as many as queries you want and from different tables, counts, columns etc. Just one thing, The result of each query should have a unique value, thus you can’t do select * !!
Or something like: select country_name FROM world.
cool ! isn’t it ?
Tags: database, MySQL, sql, stored procedure, sybase
7 Comments
Comments RSS
TrackBack Identifier URI
Leave a comment
Good work! Thank you very much!
I always wanted to write in my site something like that. Can I take part of your post to my blog?
Of course, I will add backlink?
Sincerely, Your Reader
Comment by Timur I. on January 27, 2009 8:52 pm
I didnt try it. But i think it will work.
if it work like that was good
Comment by Dileep on June 24, 2009 2:11 am
> this would mean I would have to make 10 connections
> to the database and 10 opens , 10 closes, phew !
This is not correct, the new MySQL protocol (version 10) allows you to have multiple queries running concurrently over the same connection. Each is assigned a uniqe statement_id by the server.
This is described in the prepared statement API part of the “MySQL Internals” document.
(But okay, most of the MySQL drivers has per default disabled use of the prepared statement API. Apparently there are some unresolved bugs in the server when using it.)
Comment by Shanesh Chavan on June 26, 2009 4:41 am
> 10 opens , 10 closes
By the way, take a look at connection pooling
> Timur I. Says:
That first comment is link spam (designed to affect Google PageRank).
Comment by Shanesh Chavan on June 26, 2009 4:46 am
Great!
Last week I needed the same and had to use somethin like:
declare @a int
declare @b int
select @a = count(ID) from table_1
select @b = count(ID) from table_b
select @a, @b
This one is much better.
Comment by lev on July 20, 2009 1:07 pm
how to work on procedures using functions???plz tell fast…
Comment by pooja on September 22, 2009 3:24 am
What to say I was looking for something like this and its so simple and fast, I wanted learn about 30plus count and I also dont want to open a connection 30 times.
but your post really help me.
thanks
Comment by Moksha on December 26, 2009 5:44 pm