Conversion failed when converting varchar to data type int

Question

CREATE PROCEDURE [dbo].[GetIssuedGatePassByGatepassType]
(
    @GatePass_Type int,
    @plant int
)
AS
    SET NOCOUNT ON;

    SELECT     
       GatePass_Type, Batch_No, Issued, Batch_Id
    FROM         
       GatePass_Batch
    WHERE     
       (Issued = 0) 
       AND (GatePass_Type = @GatePass_Type)
       AND Location_Id = @plant
    ORDER BY 
       CAST(Batch_No AS INT) ASC

Batch_no is of data type varchar in table

This gives me an error like

conversion failed varchar to data type int

Please give answer

Why you want to cast it as int in order by clause simply use order by clause without CAST function. — M.Ali, Apr 09 '14 at 09:13
[Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) - you should always use the most appropriate data type - that's what they're there for, after all! If `batch_no` is a number - ***WHY*** isn't it stored as `INT` or `DECIMAL(10,2)` or whatever?? Why `VARCHAR` - makes no sense at all. .... — marc_s, Apr 09 '14 at 09:32
What are the data types for the `Issued`, `GatePass_Type` and `Location_Id` columns? Any of those may also be forcing an implicit conversion to `int` to occur also. — Damien_The_Unbeliever, Apr 09 '14 at 09:35
+1 to @marc_s's comment. If you have a `VARCHAR` column, someone will put text in it eventually like it or no and if you make an assumption that it holds only numbers your conversion will fail. My guess is that you have rogue text data in the `Batch_No` field. — Steve Pettifer, Apr 09 '14 at 09:44
@M.Ali: Sorting numbers stored as a VARCHAR does not always result in the same ordering as if the numbers were stored as an INT datatype. For example: 1, 10, 100, 11, 12, 2, etc... — Dan, Apr 09 '14 at 09:45
@marc_s and Steve Pettifer: Many ERP systems (Dynamics Nav, Dynamics Ax, etc.) use CHAR-columns for sequential order numbers, etc... this is not something the administrators can simply change, without breaking something. So one will have to make do with the data model at hand. — Dan, Apr 09 '14 at 09:47
actualy i want to store special character in Batch_no. so i used varchar for that — Dattatray deshmukh, Apr 09 '14 at 09:52
Well, either **it's a number** - then use a numeric type and then you can sort by it - or it's **not** a number - but then don't expect to be able to sort numerically by that column..... — marc_s, Apr 09 '14 at 11:48

score 0 · Answer 1 · edited May 23 '17 at 12:13

CAST'ing a VARCHAR column to an INT will result in an error if any records contains anything but numbers in the VARCHAR column. So if your VARCHAR column have alphabetical or special characters, it will fail when CAST'ing to an INT. Even when you're just trying to sort by that column.

To work around this, you could perform a check on whether the content of the column is purely numeric, before casting:

ORDER BY
    CASE WHEN ISNUMERIC(Batch_No) = 1 THEN CAST(Batch_No AS INT) ELSE 99999999 END ASC

Here, I'm sorting by Batch_No as an integer only when Batch_No is numeric. In case Batch_No is not numeric, I'm sorting by some large number, to make sure that the non-numeric Batch_No records appear at the end of the dataset.

Note that ISNUMERIC is not perfect. It will return 1 in some very special cases, where CAST(Batch_No AS INT) will still fail. For example: SELECT ISNUMERIC('$') will return 1, but SELECT CAST('$' AS INT) will result in an error. Check out the remarks here for more information. See also this SO question.

Conversion failed when converting varchar to data type int

1 Answers1