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
This question is related to
sql-server
stored-procedures
parameters
sql-like
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))
EG : COMPARE TO VILLAGE NAME
ALTER PROCEDURE POSMAST
(@COLUMN_NAME VARCHAR(50))
AS
SELECT * FROM TABLE_NAME
WHERE
village_name LIKE + @VILLAGE_NAME + '%';
I was working on same. Check below statement. Worked for me!!
SELECT * FROM [Schema].[Table] WHERE [Column] LIKE '%' + @Parameter + '%'
...
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.
Source: Stackoverflow.com