Dec 18

Multiple SQL Queries in a Stored Procedure.

Filled under: MySQL | | Trackback
Tags: , , , ,


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 ?
  • Share/Bookmark


7 Responses to “Multiple SQL Queries in a Stored Procedure.”

  1.  Timur I. Says:

    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

  2.  Dileep Says:

    I didnt try it. But i think it will work.
    if it work like that was good

  3.  Shanesh Chavan Says:

    > 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.)

  4.  Shanesh Chavan Says:

    > 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).

  5.  lev Says:

    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.

  6.  pooja Says:

    how to work on procedures using functions???plz tell fast…

  7.  Moksha Says:

    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

Leave a Reply