user6628729 user6628729 - 3 months ago 15
SQL Question

Conversion failed when converting the varchar value 'none' to data type int

I tried the following stored procedure

ALTER PROCEDURE spvec
AS
SELECT Row_number() OVER (ORDER BY (SELECT 1)) AS 'RNumber'
,vf.*
FROM LOGIN ld
,vehicle vf
WHERE (ld.fid = vf.fid)
AND ld.id <> 'NA'
AND vf.CustID <> 'None'
AND vf.Simo <> 'None'
ORDER BY Row_number() OVER (ORDER BY (SELECT 1))


there is none and NA text in row. e.g. there is not empty but there is values with none ,NA
here WHERE (ld.fid = vf.fid)

UPDATE

ld.fid is string
and vf.fid is in int

so i try to convert

ALTER PROCEDURE spvec
AS
SELECT Row_number() OVER (ORDER BY (SELECT 1)) AS 'RNumber'
,vf.*
FROM LOGIN ld
,vehicle vf
WHERE CAST((ld.FFID=vf.FFID) AS INT)
AND ld.id <> 'NA'
AND vf.CustID <> 'None'
AND vf.Simo <> 'None'
ORDER BY Row_number() OVER (ORDER BY (SELECT 1))\


this show following error on update

Incorrect syntax near the keyword 'AS'.

Incorrect syntax near ')'.

it throws the following error without update


Conversion failed when converting the varchar value 'none' to data type int.

Answer

If your tables actually consist of None and NA, then try this :

ALTER PROCEDURE spvec
AS
SELECT Row_number() OVER (ORDER BY (SELECT 1)) AS 'RNumber'
    ,vf.*
FROM LOGIN ld
    ,vehicle vf
WHERE (ld.fid = cast(vf.fid as varchar(max)))
    AND cast(ld.id as varchar(2)) <> 'NA'
    AND cast(vf.CustID as varchar(4)) <> 'None'
    AND cast(vf.Simo as varchar(4)) <> 'None'
ORDER BY Row_number() OVER (ORDER BY (SELECT 1))