Vijjendra Vijjendra - 1 month ago 12
SQL Question

Pass Output Value of one stored procedure to another stored procedure

I want to use output value of one stored procedure in another stored procedure .

Stored procedure 1:

Create PROCEDURE [dbo].[usp_AddUpdateUser]
@UserId INT,
@Email varchar(50),
@FirstName varchar(50)
AS
BEGIN
MERGE [User] AS target
USING (SELECT @UserId) AS source (Id)
ON target.Id = source.Id

WHEN MATCHED THEN
UPDATE
SET Email = @Email,
FirstName = @FirstName

WHEN NOT MATCHED THEN
INSERT (Email, FirstName)
VALUES (@Email, @FirstName)

OUTPUT inserted.Id;
END


Now I want to use the inserted Id of above stored procedure to below stored procedure:

ALTER PROCEDURE usp_AddUpdateDealer
(@Id INT,
@DealerName varchar(55),
@Email varchar(55),
@UserId INT)
AS
BEGIN
DECLARE @NewUserId INT

EXEC @NewUserId = usp_AddUpdateUser @UserId, @Email, @DealerName

MERGE Dealer AS target
USING (SELECT @Id) AS source (Id) ON target.Id = source.Id

WHEN MATCHED THEN
UPDATE
SET @DealerName = @DealerName,
Email = @Email,
UserId = @NewUserId

WHEN NOT MATCHED THEN
INSERT (DealerName, Email, UserId)
VALUES (@DealerName, @Email, @NewUserId)

OUTPUT inserted.Id;
END


@NewUserId
not gives the output value.

How can I got the output option of the
usp_AddUpdateUser
stored procedure to use that in next statement?

Answer
   ALTER PROCEDURE usp_AddUpdateDealer 
   (     
      @Id INT,
      @DealerName varchar(55),
      @Email varchar(55),
      @UserId INT
   )
     AS      
    BEGIN
   DECLARE @t table(NewUserId INT )
   INSERT @t(NewUserId)
   EXEC @NewUserId = usp_AddUpdateUser @UserId,@Email,@DealerName

   DECLARE @NewUserId INT 
   SELECT @NewUserId = NewUserId FROM @t

    MERGE Dealer AS target
    USING (SELECT @Id) AS source (Id)
    ON target.Id = source.Id
    WHEN MATCHED THEN
   UPDATE 
       SET @DealerName = @DealerName,
           Email = @Email,
           UserId=@NewUserId
           WHEN NOT MATCHED THEN
           INSERT (DealerName,Email,UserId)
          VALUES (@DealerName,@Email,@NewUserId)
       OUTPUT inserted.Id;
    END