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

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? — andrea
– andrea, Commented Nov 7, 2016 at 0:48
You misunderstand both what PRINT and RETURN does. Print pushes a value to the compiler while RETURN pushes a value to the caller, such as it defined by the query. Those rules are defined in MSDN's documentation Typically, using a FUNCTION would be appropriate but you can also use OUPUT statement in your proc itself to return a value to the caller. — clifton_h
– clifton_h, Commented Nov 7, 2016 at 1:09
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. — andrea
– andrea, Commented Nov 7, 2016 at 17:26
Umm, you can return tabular functions/return different datatypes. I think you are giving up a bit too early. — clifton_h
– clifton_h, Commented Nov 7, 2016 at 17:46
Thank you. I know about using tabular functions, but i had misunderstood the use of the command "return" in procedure. Now i know that i can only return int datatype. I get confused in the example tutorial online of procedure returning the salary of the employees, and i thought i could return any information, including the employee's name. — andrea
– andrea, Commented Nov 7, 2016 at 18:04

Nayak · Accepted Answer · 2016-11-07 02:32:56Z

2

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

answered Nov 7, 2016 at 2:32

Nayak

4733 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

andrea Over a year ago

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.

Nayak Over a year ago

This page has information on both the approaches of retuning data from a stored procedure: msdn.microsoft.com/en-us/library/ms188655.aspx Though many of the usages I have seen refer 'return' for returning an error code, but it need not be the only case. In your example, you could return the value @howmuch as a return value instead of output parameter. Often, I use return code for returning deleted count, inserted count etc.

Collectives™ on Stack Overflow

SQL Server output and return value in procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related