SqlServer output and return value in procedure

Question

I'm new to sqlserver, 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 --with return doesn't work!!
--select @name with select it works !!
--@name as an output parameter it works!!
GO
-- execute the procedure. It gives me error.
DECLARE @num_rows int, @who varchar(100)
EXECUTE @who=info 100, @num_rows OUTPUT
select @num_rows,@who

I have noticed that: If instead of using "return" I use "print" to visualize the "@name", the procedure works, and i see "@name". It also works well if instead of "return" I put "@name" as an output parameter. It seems that the error is only when using "return" to return @name. Why is this? What i'm missing to understand?

Answer 1

It looks like you are attempting to give the value of @name back to the caller, but that is not the purpose of RETURN. When you give an argument after RETURN it must be an integer which indicates the status of the procedure.

Better off declaring @name as an output parameter or SELECT @name.

Quote from Ref. https://msdn.microsoft.com/en-us/library/ms174998.aspx

RETURN

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.

• andrea: Yes, now I understand. I did not understand the use of return. I thought I could use a return with all types of data, and not just as int. Now it is clear. Thank you.

Answer 2

You should not use return clause that way.

If you want to ouput variables, just need to set them in the proc and declare them when you call it. So if you also want to output the name of the employee, you have to declare it as a parameter.

So :

CREATE PROCEDURE info (@id int , @howMuch int OUTPUT, @name varchar(100) output) AS
select @howMuch =count (*) from emp;
select @name=ename from emp where empno=@id

and then you call the proc :

DECLARE @num_rows int, @who varchar(100)
EXECUTE info @id= 100, 
 @howMuch=@num_rows OUTPUT, 
 @name=@who OUTPUT
select @num_rows,@who

• andrea: Yes, now I understand. I did not understand the use of return. I thought I could use a return with all types of data, and not just as int. Now it is clear. Thank you.