SQL Server output and return value in procedure

Question

I'm new to SQL Server, and i have this error that i was not able to fix:

Msg 245, Level 16, State 1, Procedure info, Line 11
Conversion failed when converting the varchar value 'Steven' to data type int

Can you please show me where is the error, and how can I fix it?

The code is as follow thank you.

CREATE PROCEDURE info (@id int, @howMuch int OUTPUT) 
AS
   SELECT * 
   FROM emp 
   WHERE empno = @id;

   SELECT @howMuch = COUNT(*) 
   FROM emp;

   DECLARE @name varchar(100)

   SELECT @name = ename 
   FROM emp 
   WHERE empno = @id

   RETURN @name

-- execute the procedure. It throws error.
DECLARE @num_rows int, @who varchar(100)
EXECUTE @who = info 100, @num_rows OUTPUT

SELECT @num_rows, @who

Answer 1

The return value of a stored procedure should always be of integer data type. If you use a function, you could return other data types, but you can have only one value returned.

As you need two values out of the stored procedure, you could use two output parameters...

CREATE PROCEDURE info2 (@id int , @howMuch int OUTPUT, @name varchar(100) OUTPUT) AS