将数据类型 varchar 转换为 int 时出错 [英] Error converting data type varchar to int

问题描述

我正在尝试使用基于 varchar 类型的Name"列的输入值的存储过程返回item"表的ItemId"列值，但是每当我将任何值传递给存储过程时，它都会返回我有一个错误:将数据类型 varchar 转换为 int 时出错.

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

我是这样称呼它的:

RetrieveId 'asf'

推荐答案

您必须匹配参数:RETURN 不会填充 OUTPUT 参数:您对 @itemid 的分配会做到这一点.

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'

另外，你的存储过程太复杂了.RETURN 不是从存储过程返回数据的好选择，并且 EXISTS 是不必要的.在这种情况下，如果找不到，@itemId 将为 NULL

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

这篇关于将数据类型 varchar 转换为 int 时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！