CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-proceduresmerge
Vijjendra
Vijjendra

Reputation: 25223

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?

Upvotes: 0

Views: 923

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44316

   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

Upvotes: 1

Related Questions