7
Answers

Sql Query For Like Operator in Stored Procedure

Mohit Kala

Mohit Kala

7y
210
1
How to Use Like operator in Left Outer Join 
Like
 
select * from employee e
LEFT OUTER JOIN City c On c.CityID-e.CityID
 
Where LIke c.CityName '%Pune%' 
Answers (7)
1
Amit Kumar

Amit Kumar

NA 3.5k 203.6k 7y
Hi, 
    Use always  single quote '%'  in sql not double quote "%" dear.
 
 
0
Bharathi Raja

Bharathi Raja

NA 400 6k 7y
  1. SELECT * FROM employee  
  2. WHERE CityName LIKE '%Pune%';  
0
Suraj Kumar

Suraj Kumar

NA 1.3k 14.3k 7y
Hi Mohit,
 
You can do this as below
 
Create Procedure DynamicSearch
@SearchText varchar(50)
AS
BEGIN
declare @SqlQuery nvarchar(2000)
set @SqlQuery= 'select * from employee e
LEFT OUTER JOIN City c On e.CityID = c.CityID
where name LIKE '''+'%' +@SearchText+ '%' + ''''
 
print @SqlQuery --If you want to see the output of query
EXECUTE sp_executesql @SqlQuery
 
END
 
 
0
Ankit Sharma

Ankit Sharma

NA 8.8k 140.9k 7y
Hi Mohit,
 
Please try the below update query and let me know if it workd for you or not
 
  1. set @sqltext= 'Select HR.HotelID AS SearchID from HotelRegistration AS HR  
  2. LEFT OUTER JOIN Category cat on cat.CategoryID=hr.CategoryID  
  3. WHERE HR.IsDeleted <> 1 AND HR.HotelName LIKE ''%'+@SearchText+'%''  
  4. OR HR.[Address] LIKE ''%'+@SearchText+'%''  
  5. OR cat.CategoryName LIKE ''%'+@SearchText+'%'''  
  6.   
  7. exec (@sqltext)  
 
 
 
0
Mohit Kala

Mohit Kala

NA 132 2.2k 7y
but i am using this query in dynamic storedprocedure like this
 
'Select HR.HotelID AS SearchID from HotelRegistration AS HR
LEFT OUTER JOIN Category cat on cat.CategoryID=hr.CategoryID
WHERE HR.IsDeleted <> 1 AND HR.HotelName LIKE ''%''+@SearchText+''%''
OR HR.[Address] LIKE ''%''+@SearchText+''%''
OR cat.CategoryName LIKE ''%''+@SearchText+''%''
 
 
0
Mukesh Nayak

Mukesh Nayak

NA 1.5k 317.4k 7y
Hi,
 
We can write the query as following :
  1. SELECT * FROM Employee emp  
  2. LEFT OUTER JOIN City city ON city.CityID = emp.CityID   
  3. WHERE  city.CityName LIKE '%Pune%'   
 Regards,
Mukesh 
0
Amit Kumar

Amit Kumar

NA 3.5k 203.6k 7y
Hi,
 
your query like operator come before field name. but it comes after field name.
 
Remove like operator before c.CityName and use it after field name.
 
exp: //use below Correct Query
select * from employee e
LEFT OUTER JOIN City c On c.CityID-e.CityID
Where c.CityName LIke '%Pune%'
 
Regards,
Amit