将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屋！