Union results of two Stored Procedures in Oracle

zeeshanpas
 
on Mar 02, 2022 12:37 AM
1358 Views

How to union these two procedures and get the result in oracle SQL.

PROCEDURE getSessionforRecording (
       isessionids   IN VARCHAR2,
       ischoolname   IN VARCHAR2,
       iprojectname  IN VARCHAR2,
       istudentname  IN VARCHAR2,
       itutorname    IN VARCHAR2,
       ifromdate     IN VARCHAR2,
       itodate       IN VARCHAR2,
       ocursor       OUT t_cursor
   ) IS
 BEGIN
  
 OPEN ocursor FOR SELECT
 (ses.session_id)session_id,
 (ses.board_refid)wb_session_id,
 (sf.session_rating)recording_priority,
  sf.tutor_rating,
  ses.subject_name,
 (ses.course_name) topic_name,
 (ses.lesson_objective ) question,
 ( st.first_name|| ','|| st.last_name ) student_name,
 ( tut.first_name|| ','|| tut.last_name ) tutor_name,
 ( sc.name ) franchise_name,
 ( prgm.name ) project_name
  
 FROM
  
    fev_oltsession                   ses
                            LEFT OUTER JOIN fev_student_oltsession           ss ON ses.session_id = ss.session_id
                            LEFT OUTER JOIN fev_school                       sc ON ses.school_id = sc.school_id
                            
                            LEFT OUTER JOIN fev_oltsession_tutor_feedback    tf ON ses.session_id = tf.session_id
                                                                                  
                            LEFT OUTER JOIN fev_oltsesson_student_feedback   sf ON  ses.session_id = sf.session_id
                                                                                   
                            LEFT OUTER JOIN fev_student                      st ON ss.student_id = st.student_id
                            
                            LEFT OUTER JOIN fev_tutor_oltsession             ts ON  ses.session_id = ts.session_id
                                                                         
                            LEFT OUTER JOIN fev_tutor                        tut ON ts.tutor_id = tut.tutor_id
                           
                        
                            LEFT OUTER JOIN fev_program                      prgm ON ses.program_id = prgm.program_id
  
                           WHERE
                                
                                 nvl(sc.is_test_data, 0) != 1
                                 AND
                                ses.session_id IN(
                                SELECT
                                    column_value
                                FROM
                                    TABLE(split_studentsfevtut(nvl(isessionids,'%')))
                            )
                                 AND ses.status=2
                                 AND (sf.tutor_rating <4)
                                 AND upper(st.first_name
                                           || ' '
                                           || st.last_name) LIKE upper(nvl(istudentname, '%'))
                                 AND upper(tut.first_name
                                           || ' '
                                           || tut.last_name) LIKE upper(nvl(itutorname, '%'))
                                 AND upper(sc.name) LIKE upper(nvl(ischoolname, '%'))
                                 AND upper(prgm.name) LIKE upper(nvl(iprojectname, '%'))
                                 AND ses.scheduled_starttime >= to_date(ifromdate, 'mm/dd/yyyy hh:mi:ss am')
                                 AND ses.scheduled_starttime <= to_date(itodate, 'mm/dd/yyyy hh:mi:ss am');
   
 END;

Note:All columns are same except 1 that is sf.sessionrating(1st procedure) and sf.tutoringrating(2nd procedure)

Download FREE API for Word, Excel and PDF in ASP.Net: Download
arjunv
 
on Mar 02, 2022 02:57 AM

Hi zeeshanpas,

UNION is for SELECT not stored procedure.

You have to use a temp or variable table like below example.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

Stored Procedure

-- EXEC Customers_GetCustomers	
CREATE PROCEDURE [dbo].[Customers_GetCustomers]	
AS
BEGIN
    SET NOCOUNT ON;

    SELECT CustomerID
           ,Name
           ,Country
    FROM Customers
END

Query

DECLARE @Customers TABLE
(
	[CustomerId] [int],
	[Name] [varchar](100),
	[Country] [varchar](50)
)

INSERT INTO @Customers 
EXEC  [Customers_GetCustomers]

INSERT INTO @Customers 
EXEC  [Customers_GetCustomers]

SELECT * FROM @Customers

Output