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:*/
CustomerID CustomerName Sex
4 London male
6 Mannheim famle
7 Strasbourg famle
8 Madrid famle
10 Tsawassen male
11 Londonmale male
14 Bern male
------解决方案--------------------
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