1

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
andrea
  • 396
  • 2
  • 12
  • 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 Nov 07 '16 at 00: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](https://msdn.microsoft.com/en-us/library/ms187926.aspx) 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 Nov 07 '16 at 01:09
  • 1
    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 Nov 07 '16 at 17:26
  • Umm, you can return tabular functions/return different datatypes. I think you are giving up a bit too early. – clifton_h Nov 07 '16 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 Nov 07 '16 at 18:04

1 Answers1

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
Nayak
  • 473
  • 3
  • 10
  • 1
    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 Nov 07 '16 at 17:29
  • This page has information on both the approaches of retuning data from a stored procedure: https://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. – Nayak Nov 07 '16 at 21:48