CODEWITHSUNDEEP
sqlsql-server
D.Trump123
D.Trump123

Reputation: 37

Select query an int or nvarchar value

I'm trying to create a search function that is able to search by condition, platenumber and maximum volume using a select statement: 

select Condition, PlateNumber, MaximumVolumeLoad 
from [Truck Table] 
where Condition=@id OR PlateNumber=@id OR MaximumVolumeLoad>=@id

However, the problem is My MaximumVolumeLoad column is set into int and whenever I search for the condition, I get this error:

Conversion failed when converting the nvarchar value 'good' to data type int.

Is there any way where I can search for them at the same time without having to create another query?

Upvotes: 0

Views: 2162

Answers (2)

Udai Krishnan
Udai Krishnan

Reputation: 76

May be an alternative to what has been suggested by Gordon is to convert the column to varchar before the compare. This should behave in the same way.

select Condition, PlateNumber, MaximumVolumeLoad
from [Truck Table] tt
where Condition = @id or
  PlateNumber = @id or
  cast(MaximumVolumeLoad as varchar(10)) >=  @id;

Note: Forgot to add that it assumes that you will have alphabets in the @id. Based on ASCII character set numbers come before alphabets. So this should work as expected if it is any string that is entered. But is you check for '2' >= '+' the results could go wrong. (if @id has a value of a '+')

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

This seems like a bad idea, but you can do it by converting the value to a number:

select Condition, PlateNumber, MaximumVolumeLoad
from [Truck Table] tt
where Condition = @id or
      PlateNumber = @id or
      MaximumVolumeLoad >= try_convert(int, @id);

Note that if the value is not a valid integer, this will return NULL, so it will never match MaximumVolumeLoad. Presumably, this is the correct behavior.

Upvotes: 2

Related Questions