How Can i Let Stored Procedure Returns Varchar Value?

Question

Here is my sample:

ALTER PROCEDURE EmpFirstName
    @myParam int,
    @empFName varchar(20) output
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @empFName = empfname
    FROM FE_QEMP
    WHERE empno = @myParam
END
GO

myParam is the input and empFName will carry the output, so the procedure should only take 1 parameter since empFName is the output, but in my case i get this error:

Msg 201, Level 16, State 4, Procedure EmpFirstName, Line 0 Procedure or function 'EmpFirstName' expects parameter '@empFName', which was not supplied.

This is the way i called the procedure:

DECLARE @returnValue varchar(20)
EXEC @returnValue = EmpFirstName 10
SELECT 'Return Value ' = @returnValue

Answer 1

Return values and output parameters are two different things. If you want to call it with an output parameter, you'd do it like this:

EXEC EmpFirstName 10, @returnValue OUTPUT

SELECT 'Return Value ' + @returnValue

If you want to call it in the manner that you described in your example, then you need to alter the stored procedure to state RETURNS VARCHAR(20) and remove the output parameter. To return a value, you have to explicitly call return. In your example, you'd declare a variable, assign it in the select statement, then call return @varName.