How to execute a stored procedure in another stored procedure on sql server

Question

I am working on SQL Server 2008.

And I have a stored procedure in which I execute another usp.

Here I want to get the output of the second usp and return the main output parameter usp accordingly.

But my main usp always returns the second usp value with the main usp value.

But I want to return only the main value of usp.

The following is my procedure:

ALTER Proc [dbo].[usp_ChangeStatus](@id int,@Status int,@Name varchar(300),@Success int output ) as set @Success=0 begin try if exists(Select * from tbl_abc where id= @id) Begin if(@Status =1) begin ----Try-Catch block---- begin try declare @AddHostStatus as int set @AddHostStatus=0 exec @AddHostStatus =usp_xyz @Name,0,@Address if(@AddHostStatus=-3) begin set @Success=1 end end try begin catch set @Success=0 end catch ----End-Try-Catch block---- end if(@Success=1) begin --do something here end End end try begin catch set @Success=0 end catch select @Success 

Answer 1

try it

Suppose you have one stored procedure like this

First stored procedure:

 Create PROCEDURE LoginId @UserName nvarchar(200), @Password nvarchar(200) AS BEGIN DECLARE @loginID int SELECT @loginID = LoginId FROM UserLogin WHERE UserName = @UserName AND Password = @Password return @loginID END 

Now you want to call this procedure from another stored procedure, as shown below

Second stored procedure

 Create PROCEDURE Emprecord @UserName nvarchar(200), @Password nvarchar(200), @Email nvarchar(200), @IsAdmin bit, @EmpName nvarchar(200), @EmpLastName nvarchar(200), @EmpAddress nvarchar(200), @EmpContactNo nvarchar(150), @EmpCompanyName nvarchar(200) AS BEGIN INSERT INTO UserLogin VALUES(@UserName,@Password,@Email,@IsAdmin) DECLARE @EmpLoginid int exec @EmpLoginid= LoginId @UserName,@Password INSERT INTO tblEmployee VALUES(@EmpName,@EmpLastName,@EmpAddress,@EmpContactNo,@EmpCompanyName,@EmpLoginid) END 

As you saw above, we can call one stored procedure from another