使用带有存储过程参数的 LIKE 运算符 [英] Using LIKE operator with stored procedure parameters

问题描述

我有一个存储过程，它使用 LIKE 运算符在其他一些参数中搜索卡车位置

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

推荐答案

@location nchar(20) 的数据类型应该是 @location nvarchar(20)，因为nChar 有固定长度(用空格填充).
如果 Location 也是 nchar，则您必须对其进行转换:

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+'%' ...   

要使用和 AND 条件启用可为空参数，只需使用 IsNull 或 Coalesce 进行比较，这在您使用 OR 的示例中不需要.

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

这篇关于使用带有存储过程参数的 LIKE 运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！