• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 514
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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