使用LIKE运算符和存储过程参数

I have a stored procedure that uses the LIKE operator to search for a truck location among some other parameters

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

   @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

我将其他参数置零并仅按位置搜索，但即使我使用该位置的全名，它也始终不返回任何结果

3 个解决方案

#1

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:

@location nchar（20）的数据类型应为@location nvarchar（20），因为nChar具有固定长度（用空格填充）。如果Location也是nchar，你将不得不转换它：

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

要使用和AND条件启用可空参数，只需使用IsNull或Coalesce进行比较，这在使用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))

#2

I was working on same. Check below statement. Worked for me!!

我正在努力。检查以下声明。为我工作!!

SELECT * FROM [Schema].[Table] WHERE [Column] LIKE '%' + @Parameter + '%'

#3

-3

EG : COMPARE TO VILLAGE NAME

EG：比较村庄名称

ALTER PROCEDURE POSMAST
(@COLUMN_NAME VARCHAR(50))
AS
SELECT * FROM TABLE_NAME
WHERE 
village_name LIKE + @VILLAGE_NAME + '%';

#1