Calling a stored procedure from another SQL Server Database Engine
You are missing the schema. The format is [LinkServer].[Database].[Schema].[Object]
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.
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.
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 to return an id and use it directly in another stored procedure?
If you want to return something from stored procedure to the context of SQL query execution you may use a return statement or an output parameter. I would suggest you to use the second option. The first one is generally intended to return status of procedure execution.
ALTER PROCEDURE [dbo].[InsertAddress_DBO]
@Name VARCHAR(50),
@Address_ID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[Address]([Address_Name])
VALUES (@Name)
SET @Address_ID = SCOPE_IDENTITY()
END
Than you can use returned value in your outer procedure
ALTER PROCEDURE [dbo].[InsertEstablishmentByStrings_DBO]
@Establishment_Name VARCHAR(50),
@Address_Name VARCHAR(50),
@Documentation_Text VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Address_ID INT ,
@Documentation_ID INT
EXEC [dbo].[InsertAddress_DBO]
@Address_ID = @Address_ID OUTPUT,
@Name = "rue de la banchiesserie 85 Golback"
...
END
An OUTPUT INSERTED clause you used doesn't returns data to the query execution context but send them to the output stream.
Related Topics
Postgresql Changing Data Directory in Ubuntu
How to Get Week Start and End Date String in Postgresql
How to Gracefully Include Formatted SQL Strings in an R Script
What's the Easiest Way to Preview Data from an Image Column
Find SQL Table Name with a Particular Column
Alternative to Listagg in Oracle
SQL Function Return-Type: Table VS Setof Records
Cakephp See the Compiled SQL Query Before Execution
Fast Relational Method of Storing Tree Data (For Instance Threaded Comments on Articles)
Which Is Better: Bookmark/Key Lookup or Index Seek
Oracle Insert from Select into Table with More Columns
Postgresql Generate_Series of Months
T-SQL Conditional Where Clause
SQL Why Is Select Count(*) , Min(Col), Max(Col) Faster Then Select Min(Col), Max(Col)
Column Conflicts with the Type of Other Columns in the Unpivot List