You must Sign In to post a response.
  • Category: SQL Server

    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
    Please check in INSERTYN,UPDATEYN,... in these your using int instead of nVarchar data type

  • #628349
    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
    what is the output your are getting in @result

  • #628358
    my output is - YYYY

  • #628386
    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
    return will give only integer values

  • #628390
    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
    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
    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
    @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.