CODEWITHSUNDEEP
sql-serverstored-proceduresparameterssql-like
Scarnet
Scarnet

Reputation: 738

Using LIKE operator with stored procedure parameters

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

Upvotes: 26

Views: 155129

Answers (8)

Deepak Sharma
Deepak Sharma

Reputation: 1

The above condition for LIKE is wrong, you need to use LIKE command is COL1 like '%@Variable%'

Upvotes: -1

Aruna Prabhath
Aruna Prabhath

Reputation: 242

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

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

Upvotes: 0

Ashok Chauhan
Ashok Chauhan

Reputation: 11

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
)

Upvotes: 0

Ashok chauhan
Ashok chauhan

Reputation: 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

Upvotes: -1

Jason Dimmick
Jason Dimmick

Reputation: 500

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

Upvotes: -1

Singh T
Singh T

Reputation: 161

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

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

Upvotes: 7

Ranga
Ranga

Reputation: 13

EG : COMPARE TO VILLAGE NAME 

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

Upvotes: -5

bummi
bummi

Reputation: 27367

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

Upvotes: 46

Related Questions