How to Call a Stored Procedure from Another Stored Procedure

Call one stored procedure to another stored procedure in DB2

I solved this problem,
So solution is like If we want to execute proc using sql command then syntex is like below,

call Proc2('My Name');

We can use this same approach inside our proc also.
For that we have to follow some steps. Lets say that our above sql call is statement that we want to execute. we are going to convert that statement into String and pass necessary parameter by concating variable values. Then execute statement.

CREATE OR REPLACE PROCEDURE Proc1()
 IS
  Declare myName in varchar;
  -- stmt variable is to execute our proc
  STMT VARCHAR(4000);
 BEGIN 
  Select fname into myName from student where fname='x'; // is returning unique value
  -- this is our logic
  STMT :='call Proc2('||myName||')';
  EXECUTE IMMEDIATE STMT;
 END;

How can I call a stored procedure from another stored procedure

In First Stored Procedure, return the date as an out parameter

In Second Stored Procedure,

DECLARE @examDate DateTime
EXECUTE GetExamDate @examDate OUTPUT

Then you can use the value of @examDate, which is the result from 1st SP.

Calling a stored procedure from another stored procedure does not produce results

Using OUTPUT you can only get one single row.

You can use temp tables to share data between procedures. For example:

CREATE PROCEDURE sp_1 @par1 int,
                          @par2 bit,
                          ... AS
   ...
   INSERT/UPDATE/DELETE #mytemp
GO

CREATE PROCEDURE sp_2 AS
   DECLARE ...
   CREATE TABLE #mytemp (col1 int     NOT NULL,
                         col2 char(5) NULL,
                        ...)
   ...
   EXEC sp_1 @par1, @par2 ...
   SELECT * FROM #mytemp
GO

In addition, you have many other options described in an a great article written by Erland Sommarskog

UPDATE - Safety:

If temp table declared as #tempTable, then it is safe as those temp tables can only be accessed by the creating connection, and will be exist just only during of the execution of the stored procedure. However, if you create a global temp table (with two signs ##tempTable), then those tables are not safe as they are exposed to all connections of the SQL instance.

UPDATE - Availability:

As MSDN says:

A local temporary table created in a stored procedure is dropped
automatically when the stored procedure is finished. The table can be
referenced by any nested stored procedures executed by the stored
procedure that created the table. The table cannot be referenced by
the process that called the stored procedure that created the table.

So in our case, sp_2 is a outer stored procedure and sp_1 is a nested stored procedure.

Calling a Sybase stored procedure from another stored procedure without displaying called stored procedure results

your syntax is incorrect for normal table in ASE. But for ASE, there's a special table name RPC table can map the output of procedure to a table format output. Maybe that's what you are looking for...And that also can be called from remote ASE.
Here's a sample --

use omni_rpc 
go 

create table rmtbl 
( 
aint int null, 
bchr char(10) null, 
cchr char(10) null 
) 
go 

insert rmtbl values (11, "b_row1", "c_row1") 
insert rmtbl values (22, "b_row2", "c_row2") 
insert rmtbl values (33, "b_row3", "c_row3") 
insert rmtbl values (44, "b_row4", "c_row4") 
insert rmtbl values (55, "b_row5", "c_row6") 
go 

create proc procImm @Colnames varchar(100), @NameT varchar(20), @nameCol varchar 
(20), @value char(2) 
as 
execute ('select ' + @Colnames + ' from ' + @NameT + ' where ' 
+ @nameCol + ' = ' + @value) 

Here @NameT and @Colnames are command parameters, and @value is a search parameter based on the terms defined at the beginning of the paper. 

In the local server: 

use test 
go 
sp_addobjectdef myrpc_imm, "THIS.omni_rpc..procImm", "rpc" 
go 
(return status = 0) 

create existing table myrpc_imm 
( 
NameT varchar(20), 
nameCol varchar(20), 
value varchar(10) 
) 
external procedure at "THIS.omni_rpc..procImm" 
go 
select * from myrpc_imm 
where NameT = 'rmtbl' and nameCol = 'aint' and value = '33' 
go 
NameT                nameCol              value 
-------------------- -------------------- ---------- 

(0 rows affected) 

dbcc traceon(11225) 
go 
00:00000:00017:2004/04/01 12:18:47.03 server DBCC TRACEON 11225, SPID 17 
DBCC execution completed. If DBCC printed error messages, contact a user with 
System Administrator (SA) role. 

select * from myrpc_imm 
where NameT = 'rmtbl' and nameCol = 'aint' and value = '33' 
go 
NameT                nameCol              value 
-------------------- -------------------- ---------- 
33                   b_row3               c_row3 

(1 row affected) 

Execute stored procedure inside another stored procedure in snowflake

Not sure, how you are doing it. I am able to do it as below

CREATE OR REPLACE procedure FIRST_PROC()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT 
AS
$$
    A = 'DONE';
    return A;
$$;

CREATE OR REPLACE procedure SECOND_PROC()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT 
AS
$$
    query = 'CALL FIRST_PROC()';
    A= snowflake.execute({ sqlText:query });
    A.next();    
    return_val = A.getColumnValue(1);
    return return_val;
$$;

CALL SECOND_PROC();

Return value from one stored procedure to another

Add

@Status int OUTPUT

in the pro_ForeignKeyCheck so it starts with

CREATE PROCEDURE dbo.pro_ForeignKeyCheck1
@tableName VARCHAR(100),
@columnName VARCHAR(100),
@idValue int,
@Status int OUTPUT

and at the end of it did as follow

--select coalesce(@fkFound,0)
select @Status = coalesce(@fkFound,0)
--return 0

stop the last to line and add new one

In the other stored procedure, call it as follows

EXEC  pro_ForeignKeyCheck1 'tb_M_admin','admin_id', 0 ,@exit output 
select @exit

and now the return value will be used.

Thanks to all

---

Related Topics