1. Discussion Forums
    2. .NET
    3. SQL Server
You must Sign In to post a response.
  • Category: SQL Server
    Points:5

    Conversion failed when converting the nvarchar value 'YYYY' to data type int.

    Hi,

    When i am execute this procedure then i got output as 'Conversion failed when converting the nvarchar value 'YYYY' to data type int.'

    please check this procedure and sugesst me how to occur this error in logon_Setting tables all the column has nvarchar datatype.



    ALTER Proc [dbo].[GetUserRights](@UserNAme nvarchar(15),@Type nvarchar(3),@ReturnVal nvarchar(5) output)
    as
    begin
    declare @a nvarchar(1),@b nvarchar(1),@c nvarchar(1),@d nvarchar(1),@result nvarchar(5);
    select @a=INSERTYN,@b=UPDATEYN,@c=DELETEYN,@d=AUTHORIZEYN from logon_setting where type=@Type and user_name=@UserNAme
    set @result=@a+@b+@c+@d;
    set @ReturnVal=@result
    return @ReturnVal;
    end
  • #628348

    Points:1
    Please check in INSERTYN,UPDATEYN,... in these your using int instead of nVarchar data type

  • #628349

    Points:0
    My Table is like that,when i print @result then output is right but when i execute procedure for return @ReturnVal then conversion error occured but i doent use int datatype in my proc and table

    USER_NAME nvarchar(20) Checked
    TYPE nvarchar(2) Checked
    INSERTYN nvarchar(1) Checked
    UPDATEYN nvarchar(1) Checked
    DELETEYN nvarchar(1) Checked
    AUTHORIZEYN nvarchar(1) Checked

  • #628357

    Points:0
    what is the output your are getting in @result

  • #628358

    Points:0
    my output is - YYYY

  • #628386

    Points:3
    We should not use return i think. I used print to display of @ReturnVal. check this

    Alter Proc [dbo].[GetUserRights](@ReturnVal nvarchar(5) output)

    as
    begin
    declare @a nvarchar(1),@b nvarchar(1),@c nvarchar(1),@d nvarchar(1),@result nvarchar(5);
    set @result='yyyy';
    set @ReturnVal=@result;
    print @ReturnVal;
    --return @ReturnVal;

  • #628388

    Points:1
    return will give only integer values

  • #628390

    Points:1
    Hi,
    sujatha coding will solve your problem But no need print @ReturnVal.if you run that stored procedure result will come because @ReturnVal is output variable.


    Thanks & Regards
    G.Renganathan
    Nothing is mine ,Everything is yours!!!

  • #628391

    Points:2
    Your stored proc has a bug in it. You are trying to return a non-integer value which would through an error only!

    Please be adviced that from stroed procedure when you are using RETURN then you can ONLY RETURN INTEGER.. it won't allow any other data types.



    ALTER PROC [dbo].[GetUserRights]
    (
    	@UserNAme NVARCHAR(20),
    	@Type NVARCHAR(2),
    	@ReturnVal NVARCHAR(5) OUTPUT
    )
    AS
    BEGIN
    	DECLARE @a NVARCHAR(1), @b NVARCHAR(1), @c NVARCHAR(1), @d NVARCHAR(1);
    	
    	SELECT @a=INSERTYN, @b=UPDATEYN, @c=DELETEYN, @d=AUTHORIZEYN 
    	FROM logon_setting 
    	WHERE 
    		[type]= @Type 
    		and [user_name]= @UserNAme
    	
    	SET @ReturnVal=@a+@b+@c+@d;
    END
    go


    --Testing
    Declare @result as NVARCHAR(5)
    Exec [dbo].[GetUserRights] 'v','a', @result OUTPUT
    Select @result


    Best Regards
    Vadivel
    MVP SQL Server

  • #628395

    Points:3
    There were other issues in your SP as well. Though they aren't related to this current error most of them might create performance problems for you in future!

    1. Your table colum size and the column sizes which you have declared within the SP are not matching!!

    2. Its always best practice to avoid reserved names as your table column. You are using 2 reserved names ... user_name and type.

    3. If at all you cannot change the column names now!! then atleast put a square bracket around the column name while using it. Like [type]= @Type and [user_name]= @UserName

    4. Are you going to store unicode data into all these columns? If not, why do you want to use NVARCHAR which is wastage of space!! Use VARCHAR instead.

    Best Regards
    Vadivel
    MVP SQL Server

  • #628512

    Points:0
    @Sujatha
    Thanks For These Code I used this code in my project.


    • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.
    Submit New Thread
    Return to Return to Discussion Forum

    Top Contributors

    TodayLast 7 Days

    more...

    About dotnetspider.com

    We believe in providing quality content to our readers. If you have any questions or concerns regarding any content published here, feel free to contact us using the Contact link below.

    Quick Links
    General
    Services

    Digital Marketing by SpiderWorks Technologies, Kochi - India. ©