EXEC Stored Procedure Output to Variable in Insert Operation

Question

I have the following Stored Procedure:

Create Procedure spValueInsert
(@Param3 varchar(50) OUTPUT)
AS
BEGIN
     SELECT TOP 1 uID FROM Table2
END

Then in another Stored Procedure, I have the following syntax:

Create Procedure spMainProc
(@Col1 varchar(50), @Col2 varchar(50))
AS
BEGIN
     INSERT INTO TABLE1(Col1, Col2, Col3)VALUES(@Col1, @Col2, XXX)
END

For Col3, I want to add the result from spValueInsert. How can I do that?
I am trying the following syntax but it doesn't work.

DECLARE @OutputParameter varchar(50), @ReturnValue int
EXEC @ReturnValue = spValueInsert @OutputParameter OUTPUT
INSERT INTO Table1(Col1, Col2, Col3)VALUES (@Col1, @Col2, @OutputParameter)

Please advise. The data type for result is VARCHAR(50).
Thank you.

Answer 1

You need to assign a value to the output parameter in the first procedure:

CREATE PROCEDURE spValueInsert (
   @uID varchar(50) OUTPUT
)
AS
BEGIN
     SELECT TOP 1 @uID = uID 
     FROM Table2
     -- ORDER BY uID
END

CREATE PROCEDURE spMainProc (
   @Col1 varchar(50), 
   @Col2 varchar(50)
)
AS
BEGIN
    DECLARE 
        @uID varchar(50), 
        @ReturnValue int
    EXEC @ReturnValue = spValueInsert @uID OUTPUT
    IF @ReturnValue <> 0 RETURN @ReturnValue

    INSERT INTO Table1(Col1, Col2, Col3)
    VALUES (@Col1, @Col2, @uID)
END

Note, that even without SELECT TOP 1 @uID = uID ..., the value of the output parameter will be NULL.