Conversion failed when converting the varchar value 'Bern' to data type int.
本帖最后由 suanleba10 于 2013-08-20 10:52:41 编辑
proc:

create proc GetName
@Id int,
@Sex varchar(10)
as

declare @ReturnVar varchar(10)
select @ReturnVar=CustomerName from Customer
where CustomerId=@Id and Sex=@Sex
return @ReturnVar

exec:

use TestForProc 
go
declare @Id int, @Sex varchar(10), @Name varchar(10)
set @Id=14
set @Sex='male'
exec @Name=GetName @Id, @Sex
select @Name

执行exec下的sql语句后报错：
Msg 245, Level 16, State 1, Procedure getid_return, Line 9
Conversion failed when converting the varchar value 'Bern' to data type int.

请问是什么原因造成的？
谢谢！
database:TestForProc
btw:

  
/*table: Customer*/
create table Customer
(
     CustomerID int not null primary key,
     CustomerName varchar(10),
)

/*some table contents:*/
CustomerIDCustomerNameSex
4Londonmale
6Mannheimfamle
7Strasbourgfamle
8Madridfamle
10Tsawassenmale
11Londonmalemale
14Bernmale

[解决办法]

create proc GetName
@Id int,
@Sex varchar(10)
as
declare @ReturnVar varchar(10)
select @ReturnVar=CustomerName from Customer
where CustomerId=@Id and Sex=@Sex
return @ReturnVar--问题出现在这一行. 存储过程的RETURN值是一个整数类型。你这儿RETURN的是字符型。所以从字符型(Bern)转换成整型时就报错了

[解决办法]
create?proc?GetName
@Id?int,
@Sex?varchar(10)
as
?
declare?@ReturnVar?varchar(10)
select?@ReturnVar=CustomerName?from?Customer

where?CustomerId=@Id?and?Sex=@Sex
return? 这里改成select @ReturnVar