none
convert integer to string

    Question

  • How can I convert an integer to string?

    When I use convert function to convert an integer to varchar as below, I get incorrect value of @EmployeeID  as '1'. In the Employee table, EmployeeID is of type int.

     

    I want to pass @EmployeeID and @NewID as string to another stored proc which accepts @EmployeeID and @NewID as TEXT parameters.

     
     Declare @RowID INT
     DECLARE @EmployeeID     VARCHAR
     DECLARE @NewID VARCHAR

      
       SELECT @EmployeeID  = CONVERT(varchar, EmployeeID)
       FROM dbo.Employee 
       WHERE Row = @RowID

    ...

    ...

     

    EXEC calculateSalary @EmployeeID, @NewID

     

     

    Monday, April 09, 2007 11:58 PM

Answers


  • You might wish to add a size parameter to a varchar() datatype. Using just [ varchar ] defaults to a single character -truncating the rest if the number is greater than 9.


    Your SELECT statement, using convert() 'should' be functioning ok. I would prefer using CAST( EmployeeID as varchar(5) ).


     

    Tuesday, April 10, 2007 12:20 AM
    Moderator

All replies

  • My guess would be that your EmployeeID starts with a "1", see you're declaring EmployeeID to be a varchar of length 1, so it's only storing the first character of the EmployeeID, for example:

    declare @employeeid varchar
    select @employeeid =  convert(varchar, 54321)
    select @employeeid

    returns "5" where:

    declare @employeeid varchar(5)
    select @employeeid =  convert(varchar, 54321)
    select @employeeid

    returns the whole integer.

    Tuesday, April 10, 2007 12:18 AM

  • You might wish to add a size parameter to a varchar() datatype. Using just [ varchar ] defaults to a single character -truncating the rest if the number is greater than 9.


    Your SELECT statement, using convert() 'should' be functioning ok. I would prefer using CAST( EmployeeID as varchar(5) ).


     

    Tuesday, April 10, 2007 12:20 AM
    Moderator
  • Note that varchar defaults to one character in a declare statement, but 30 in a cast/convert:

    declare @i int
    set @i = 1234567890
    select cast(@i as varchar)

    And

    select cast('12345678901234567890123456789012345678901234567890' as varchar)

    Horrible, horrible thing Smile 
    Tuesday, April 10, 2007 2:58 AM
    Moderator
  • Converting from NUMERIC data type to string never truncate the value it will produce * symbol on your output.

     

       

    Code Snippet

    Select Convert(varchar(2),99) A, cast(99as varchar(2)) B

      A    B

      ---- ----

      99   99

      

      Select Convert(varchar(2),100) A, cast(100 as varchar(2)) B
       A    B   
       ---- ----
       *    *

     

     

     

     

    Tuesday, April 10, 2007 4:49 AM
  •  Manivannan.D.Sekaran wrote:

    Converting from NUMERIC data type to string never truncate the value it will produce * symbol on your output.

     

    Very true, however that is not the problem here.  try running the code:

     

    Code Snippet

    DECLARE @ShortChar as varchar

    DECLARE @LongChar as varchar(10)

    SET @ShortChar = CONVERT(varchar, 200)

    SET @LongChar = CONVERT(varchar, 200)

    SELECT @ShortChar, @LongChar

     

    The result is:      2      200

     

    The problem is not that the convert is truncating the value but that the assignment is silently truncating the value at the length of the variable (1 character).  It is trying to put 3 characters in but can only fit the first one and so "loses" the rest.

    Tuesday, April 10, 2007 9:43 AM