CODEWITHSUNDEEP
c#sql-serverstored-procedures
Anjum
Anjum

Reputation: 681

Calling stored procedure from another stored procedure with parameters

I have a stored procedure with takes 2 parameters (StudentID and CurrentSmester) as input and return me table with fields :

  1. Course Code
  2. Update Type
  3. Update Id

This stored procedure is created by another team in my office. I can not modify it but at the same point I want to use it in my Webservice (which I am building for android) to make sure data remains consistent.

My requirement is to get :

  1. Course Code
  2. Update Type
  3. Update Id
  4. Course Title

Can I create another store procedure which will call that store procedure with parameters as I mentioned, make a join with course table to get course title too.

Is this possible ? If yes can you please guide me through its implementation.

Thanking You and Happy New Year !!

Upvotes: 1

Views: 769

Answers (3)

SanyTiger
SanyTiger

Reputation: 666

-- First Stored Procedure

CREATE PROCEDURE FirstSP
@MyFirstParam INT
AS
DECLARE @MyFirstParamValue INT
SELECT @MyFirstParamValue = @MyFirstParam * @MyFirstParam
RETURN (@MyFirstParamValue)
GO

-- Second Stored Procedure

CREATE PROCEDURE SecondSP
@SecondParam INT
AS
DECLARE @SecondParamValue INT
SELECT @SecondParamValue = @SecondParam * @SecondParam
RETURN (@SecondParamValue)
GO

-- Pass One Stored Procedure's Result as Another Stored Procedure's Parameter

DECLARE @FirstValue INT, @SeondValue INT

-- First SP

EXEC @FirstValue = FirstSP 5

-- Second SP

EXEC @SeondValue = SecondSP @FirstValue
SELECT @SeondValue SecondSP
GO

-- Clean up

DROP PROCEDURE FirstSP
DROP PROCEDURE SecondSP
GO

Upvotes: 0

M.Ali
M.Ali

Reputation: 69514

Create a new stored procedure , insert the results coming back from your existing stored procedure into a temp table, join your Course table with that temp table and you are good to go ,

something like this.....

CREATE PROCEDURE usp_NewProc 
@StudentID INT ,
@CurrentSmester INT 
AS
BEGIN
  SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL 
  DROP TABLE #temp

CREATE TABLE #temp 
(
CourseCode [DataType],
UpdateType [DataType],
Update Id  [DataType]
)

INSERT INTO #temp 
EXEC existsting_proc @StudentID , @CurrentSmester


SELECT t.* , C.CourseTitle
FROM #temp t INNER JOIN CourseTable C  on <join Condition>

IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL 
  DROP TABLE #temp

END

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166376

You can insert the results from the STORED PROCEDURE into a temp table (table variable or temp table) and them select from that table and join onto the Courses table to retrieve the title.

SQL Fiddle DEMO

Example code

CREATE TABLE Courses(
  CourseCode VARCHAR(50),
  CourseName VARCHAR(250)
);
INSERT INTO Courses VALUES ('A','AA'), ('B','BB');

CREATE PROCEDURE OtherTeamsSP(
  @StudentID INT,
  @CurrentSmester INT
)
AS
SELECT 'A' CourseCode,
        'FOO' UpdateType,
        1 UpdateId;

CREATE PROCEDURE MyProcedure(
  @StudentID INT,
  @CurrentSmester INT
)
AS
CREATE TABLE #SPOutput(
  CourseCode VARCHAR(50),
  UpdateType VARCHAR(50),
  UpdateId INT
 )

INSERT INTO #SPOutput
EXEC OtherTeamsSP @StudentID, @CurrentSmester

SELECT *
FROM #SPOutput s INNER JOIN
    Courses c ON s.CourseCode = c.CourseCode
DROP TABLE #SPOutput

Calling the new SP

EXEC MyProcedure 1,2

Upvotes: 0

Related Questions