Error converting data type varchar to int

Question

I'm trying to return 'ItemId' column value of 'item' table using stored procedure based on the input value of 'Name' column which is of varchar type, but whenever I pass any value to stored procedure it is returning me an error: Error converting data type varchar to int.

 create procedure RetrieveId
(@itemId int output,@Name varchar(30))
As
Begin
If exists(Select  * from item where [Name] = @Name)
Begin
 Select @itemId = itemid from item 
  where [Name] = @Name
 return @itemId
End
Else
return 1
End

This is how I'm calling it:

RetrieveId 'asf'

gbn gbn · Accepted Answer · 2011-07-20T06:33:16

You have to match parameters: The RETURN does not populate the OUTPUT parameters: your assignment to @itemid does that.

DECLARE @item int
EXEC RetrieveId @item OUTPUT, 'asf'

Also, your stored proc is too complex. RETURN is not a good choice for returning data from stored procs and the EXISTS is unnecessary. In this case, @itemId will be NULL if not found

create procedure RetrieveId
   @itemId int output,
   @Name varchar(30)
As
   Select @itemId = itemid
   from item 
   where [Name] = @Name
GO

Error converting data type varchar to int

1 Answers