Menu
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
504
The value of the parameter is used along with LIKE operator (statement) in a SELECT statement. In the above Stored Procedure, the LIKE operator (statement) works as CONTAINS where it looks for the match throughout the string value. You can also use it as STARTS WITH and ENDS WITH options as shown below.
The LIKE command is used in a WHERE clause to search for a specified pattern in a column. You can use two wildcards with LIKE : % - Represents zero, one, or multiple characters. _ - Represents a single character (MS Access uses a question mark (?) instead)
Using the CONCAT() function, we can work with user variables in LIKE clause. The syntax is as follows.
“The SQL LIKE operator allows performing logical evaluation for any matching records. Using the LIKE operator, you can specify single or multiple conditions. This allows you to perform an action such as select, delete, and updating any columns or records that match the specified conditions.
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)) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
