Issue with temp table and merging the results of two stored procedures

user_5149850 0
Viewed 104

Goal

I have two stored procedures and I am attempting to merge their results into one result set (I feel this is a good time to mention that they cannot be joined on any column).

I've been looking around and the solution seems to be using a temp table. However, while I can create the procedure fine, when I call it I get an error that the temp table is invalid.

I'm aware of local and global temp tables and sessions. But if I am calling the stored procedure that creates the table/session, I'm unsure why I will get this error.

Appreciate help in solving.

Stored procedure:

ALTER PROCEDURE usp_returnReadBook
    @DateStart DATETIME = NULL,
    @DateEnd DATETIME = NULL
AS
    INSERT INTO #tempRead
        EXECUTE [dbo].[usp_line] @DateStart,@DateEnd   

    INSERT INTO #tempRead
        EXECUTE [dbo].[usp_line2] @DateStart,@DateEnd

    SELECT * FROM #tempRead

Calling procedure:

EXEC [dbo].usp_returnReadBook @DateStart = N'2020/08/01',@DateEnd = N'2020/12/01'

Appreciate if I could get some assistance in getting my stored procedure to return the combined results of my two stored procedures.

Regards

stored-procedures tsql sql-server
1 Answers

You need to define #temp table:

ALTER PROC usp_returnReadBook
   @DateStart DATETIME = NULL,
   @DateEnd DATETIME = NULL
AS
BEGIN
  CREATE TABLE #tempRead(col1 type1, col2 type2); 

  INSERT INTO #tempRead(col1, col2) EXECUTE [dbo].[usp_line]  @DateStart,@DateEnd;  
  INSERT INTO #tempRead(col1, col2) EXECUTE [dbo].[usp_line2] @DateStart,@DateEnd;
  SELECT * FROM #tempRead;
END

The table definition has to match stored procedure output.

user_50708790
Related