Using LIKE operator with stored procedure parameters

Question

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

To correctly handle NULL values you need to check for it explicitly, e.g. `( [Date] = @Date or @Date is NULL )`. By the way, you only need to check for `'%' + @location + '%'`, the other two location tests are redundant. — HABO, May 05 '13 at 03:01
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was discontinued with the ANSI-**92** SQL Standard (more than **20 years** ago) — marc_s, May 05 '13 at 07:15
Avoir using OR as much as you can as it will degrade considerably your performance. If possible use the "In" clause instead. — Carl Verret, May 26 '21 at 16:36

bummi · Accepted Answer · 2013-05-05T06:52:22.543

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

Location like '%'+IsNull(@location,Location)+'%') will return all records if location param is null, which might not be the behaviour wanted. the question is if there is no search criteria (if null location param considered no search criteria) passed, should the SP return everything or nothing? (please fix the comment restriction here, if putting the parameter with the @ symble, S.O. will think it's at someone :) — koo9, Nov 27 '17 at 18:31

score 7 · Answer 2 · answered Feb 17 '18 at 06:22

7

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

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

answered Feb 17 '18 at 06:22

Singh T

161
1
5

Works perfectly and as expected ! – robe007 Nov 11 '19 at 23:29

score 0 · Answer 3 · edited Oct 07 '21 at 22:30

0

CREATE PROCEDURE [dbo].[Sp_schemeallotment] (
    @SchemeName NVARCHAR (255) = ' ',
    @Ind INT = 0
)
AS
  BEGIN
      IF ( @Ind = 1 )
        BEGIN
            SELECT *
            FROM   schemeallotment
        END

      IF ( @Ind = 2 )
        BEGIN
            SELECT *
            FROM   schemeallotment
            WHERE  schemename LIKE '%' + @SchemeName + '%'
        END
  END

--exec sp_SchemeAllotment @Ind=2, @SchemeName='shree'
go

It is important to assign these variable like this:

(
    @SchemeName nvarchar (255) = ' ',
    @Ind int = 0
)

edited Oct 07 '21 at 22:30

Ermenegildo

1,286
1
12
19

answered Oct 05 '21 at 10:55

Ashok Chauhan

11
1

Your code looks very similar to that in [another answer](https://stackoverflow.com/a/69448297/10871073). You should add some explanation as to how your answer is significantly different from the others. – Adrian Mole Oct 05 '21 at 14:07
2

... although, I now notice that the other answer is from a user with the same username. Are you both the same person? If so, why two accounts and two answers? – Adrian Mole Oct 05 '21 at 14:08

Aruna Prabhath · Answer 4 · 2021-10-15T08:36:57.380

0

for mysql- SELECT * FROM tablename WHERE columnanme LIKE '%'+ @location+'%';

for oracle - SELECT * FROM tablename WHERE columnanme LIKE '%'|| @location ||'%';

edited Oct 15 '21 at 08:36

answered Oct 13 '21 at 05:21

Aruna Prabhath

206
2
10

score -1 · Answer 5 · answered Feb 04 '21 at 12:00

-1

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

answered Feb 04 '21 at 12:00

Jason Dimmick

400
4
12

Ashok chauhan · Answer 6 · 2021-10-05T09:55:08.230

-1

   CREATE procedure [dbo].[sp_SchemeAllotment]
    (
    @SchemeName nvarchar (255)='',
    @Ind int=0
    )
    As 
    Begin
    if (@Ind = 1)
    begin
    SELECT * FROM SchemeAllotment
    End
    if (@Ind = 2)
    begin
    SELECT * FROM SchemeAllotment where SchemeName like '%'+ @SchemeName +'%'
    end
    End
    --exec sp_SchemeAllotment @Ind=2, @SchemeName='shree'
    GO

edited Oct 05 '21 at 09:55

answered Oct 05 '21 at 09:51

Ashok chauhan

1
1

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 05 '21 at 09:52

score -5 · Answer 7 · edited Aug 05 '13 at 16:55

-5

EG : COMPARE TO VILLAGE NAME

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

edited Aug 05 '13 at 16:55

Rui Jarimba

11,166
11
56
86

answered Aug 05 '13 at 16:25

Ranga

13

you should like this code: LIKE '%' + @VILLAGE_NAME + '%' – Ahmad Shahbaz Jan 03 '21 at 12:08

Using LIKE operator with stored procedure parameters

7 Answers7

Linked