multiple result sets

Question

multiple result sets

doc1623

SSC Enthusiast

Points: 184
More actions
January 31, 2020 at 4:11 pm

#3722074

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)

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

doc1623 SSC Enthusiast Points: 184 More actions · Answer 2

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, 3 months ago by doc1623.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

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?

doc1623 SSC Enthusiast Points: 184 More actions · Answer 4

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.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

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.