Using Stored Procedure Result Set into a INSERT statement on another Stored Procedure

Database
Teradata Employee

Using Stored Procedure Result Set into a INSERT statement on another Stored Procedure

Hi, I'm converting some SQL Server SP into Teradata SP and one of the "issues" I'm facing is converting the piece of code below:

"

ALTER PROCEDURE [dbo].[PRDe_CRUZTO_PROCESSAR] 

AS

SET XACT_ABORT ON

SET NOCOUNT ON

DECLARE    @COD_INT_RMOV INT,

                  @cod_RETORNO INT,

                  @DTH_RECEB SMALLDATETIME

INSERT INTO #TEMP_PROCESSAMENTO

(    COD_INT_RMOV,

      DTH_RECEB    )

EXEC PRN_NFE.DBO.PRDe_CRUZTO_VERIFICA_RMOV_APROCESSAR

...

"

Basically, the stored procedure is using the result set returned from the stored procedure PRN_NFE.DBO.PRDe_CRUZTO_VERIFICA_RMOV_APROCESSAR as the values input to insert into #TEMP_PROCESSAMENTO table. This stored procedure (PRN_NFE.DBO.PRDe_CRUZTO_VERIFICA_RMOV_APROCESSAR) is working fine and returning the result set if I run it on either BTEQ or SQL Assistant but it won't work if used this way in Teradata (just replacing EXEC to CALL as it seems it is not accepted on INSERT statements.

My question is: is there any way to call a stored procedure from an INSERT clause? If not, which data type of variable can I use to store the stored procedure returned result set and then insert into that table?

Any advice, please let me know.

Thanks.

1 REPLY
Enthusiast

Re: Using Stored Procedure Result Set into a INSERT statement on another Stored Procedure

No, unfortunately in Teradata you can't use the SP result set in the insert statement.

Instead of returning the result set  from the SP, you can use a temp table to store the results and then can use that temp table to insert in the actual table.

Regards,