multiple result sets

  • My work has a small database for our system. It's not maintained by a DBA but the programmer. So, it has issues.

    I've done basic queries for years but it's been a long time since I've done more than a simple select/insert/update and that was on Oracle.

    I'm a maintainer/supporter of the system that uses this DB. It's a backend to a web based application with menu's. In our system each table represents a menu level (more or less). Each subsequent selection depends on the previous.

    We need a way to list different 'flows' from any selected entry in table 1.  Normally, we use Toad. I can do all the selects but my boss was hoping to do a stored procedure that would list all result sets. Can we do that? In toad I can do multiple selects and it will show different result sets but I don't know if that can translate to a stored procedure.

    I could do this in code. I'm an amateur coder and could loop through selects (using previous select results as variables) but I'm new to stored procedures and have only done basic ones. Do I need to use 'Cursors'?

     

    I haven't used them and don't have a good understanding of them.

     

    Level 1 selection (table 1 )

    Level 2 selection (table 2)

    Level 3 selection (table 3)

    Level 4 selection (table 4) (optional)

    --

    Table 5 (optional, normally will have table 4 entry)

    or

    Table 6 (optional,  normally will have table 4 entry)

     

     

     

     

  • Yes, a stored procedure can return multiple result sets.  However, you may find that that is not very useful, since you can't do anything with the results, such as inserting them into tables.  The best you can do is just display them on the screen.

    John

  • John,

     

    Thank YOU for replying but that's exactly what I need. I don't need to insert. This is only pulling info out for a report or for our team members to see the flow.

    I'm sorry to be such a beginner but ... I tried doing a stored procedure in toad with multiple selects and didn't have much success. I do have to use variables to populate from previous selections. Can some one point me to an example of this.

     

    I have something like

    declare -a @variable1

    declare -a @variable2

    declare -a @variable3

    select @variable2= (select * from table1 where column1=@variable1)

    select @variable3= (select * from table2 where column1=@variable2)

     

    select * from table3 where column1=@variable2

    select * from table4 where column3=@variable3

    if 'Y' = (select columnx from table1)

    select * from tablex where ....

     

    I'm only getting the results of the first select ..and the if's aren't working... sorry real newb

     

    • This reply was modified 4 years, 10 months ago by  doc1623.
  • You say you are "pulling info out for a report".  A report is different than what John describes as "displaying them on a screen".  What is actually running the stored procedure?  I'm a bit familiar with TOAD from my days working with Oracle but that was a long time ago.  If you are running the stored procedure in TOAD, do you have to configure TOAD to be able to actually receive the additional result sets?

  • Chris,

     

    Thank You. I can see multiple result sets when I run multiple selects in the editor but not in my stored procedure. Maybe that's an additional configuration.

  • Yes, a client program will handle executing a stored procedure that returns multiple result sets differently than running a script that has multiple queries in it.  I'm unaware if TOAD for SQL Server has that feature in it or not.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply