Using LIKE operator with stored procedure parameters

Question

I have a stored procedure that uses the LIKE operator to search for a truck location among some other parameters       location nchar 20       time time      date date AS    select         DonationsTruck VechileId  Phone  Location   Date    Time     from         Vechile  DonationsTruck     where         Vechile VechileId   DonationsTruck VechileId        and    Location like      location      or  Location like      location  or  Location like  location        or  Date   date or  Time     time    I null the other parameters and search by location only but it always returns no results even when I used the full name of the location

User · Answer

I was working on same  Check below statement  Worked for me      SELECT   FROM  Schema   Table  WHERE  Column  LIKE        Parameter

User · Answer

Your datatype for  location nchar 20  should be  location nvarchar 20   since nChar has a fixed length  filled with Spaces   If Location is nchar too you will have to convert it          Cast Location as nVarchar 200   like      location              To enable nullable parameters with and AND condition just use IsNull or Coalesce for comparison  which is not needed in your example using OR   e g  if you would like to compare for Location AND Date and Time       location nchar 20    time time   date date as select DonationsTruck VechileId  Phone  Location   Date    Time  from Vechile  DonationsTruck where Vechile VechileId   DonationsTruck VechileId and    Location like     IsNull  location Location        and  Date  IsNUll  date date  and  Time    IsNull  time Time

User · Answer

EG   COMPARE TO VILLAGE NAME   ALTER PROCEDURE POSMAST   COLUMN NAME VARCHAR 50   AS SELECT   FROM TABLE NAME WHERE  village name LIKE    VILLAGE NAME

User · Answer

WHERE           AND   Location is null OR  Location like        Location               AND   Date is null OR  Date    Date    This way it is more obvious the parameter is not used when null

[sql-server] Using LIKE operator with stored procedure parameters

Examples related to sql-server

Examples related to stored-procedures

Examples related to parameters

Examples related to sql-like