• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

Syntax error converting the varchar value

Kindly assist on the error, Thanks.

Server: Msg 245, Level 16, State 1, Procedure SP_operator_name, Line 6
Syntax error converting the varchar value 'Victor DeGuia' to a column of data type int.


create proc dbo.SP_operator_name
@operatorid int
as
declare @operatorname as varchar(100)
select @operatorname=operatorname from operator_master where operatorid=@operatorid
return @operatorname
Go

exec itpbilling.SP_operator_name @operatorid=2011
0
EdwardPeter
Asked:
EdwardPeter
1 Solution
 
BillAn1Commented:
a stored procedure can only return integer values, you cannot return a varchar value. Instead, you need to use an OUTPUT type parameter as follows :

create proc dbo.SP_operator_name
@operatorid int, @operatorname varchar(100) OUTPUT
as
select @operatorname=operatorname from operator_master where operatorid=@operatorid
GO

which can be accessed as

declare @output varchar(100)
exec SP_operator_name 2011,@output OUTPUT
select @output

alternatively, you can use a function, which can return varchar datatypes, as in :
create function dbo.FN_operator_name (@operatorID integer)
returns varchar(100)
as
begin
      declare @operatorname as varchar(100)
      select @operatorname=operatorname from operator_master where operatorid=@operatorid
      return @operatorname
end

GO

and you can access this as

select dbo.FN_operator_name (2011)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now