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

 

 

Answer 1

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.

Answer 2

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) ).

 

Answer 3

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  

Answer 4

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   
   ---- ----
   *    *

 

 

 

 

Answer 5

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.