Syntax error converting the varchar value

Posted on 2005-04-23
Last Modified: 2010-03-19
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
declare @operatorname as varchar(100)
select @operatorname=operatorname from operator_master where operatorid=@operatorid
return @operatorname

exec itpbilling.SP_operator_name @operatorid=2011
Question by:EdwardPeter
    1 Comment
    LVL 17

    Accepted Solution

    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
    select @operatorname=operatorname from operator_master where operatorid=@operatorid

    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)
          declare @operatorname as varchar(100)
          select @operatorname=operatorname from operator_master where operatorid=@operatorid
          return @operatorname


    and you can access this as

    select dbo.FN_operator_name (2011)

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now