SQL Server – Output and Return Value in Procedure

sql serverstored-procedures

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?

Best Answer

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.

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

Related Question