Sql Query For Like Operator in Stored Procedure

Mohit Kala

8y945

How to Use Like operator in Left Outer Join

select * from employee e

LEFT OUTER JOIN City c On c.CityID-e.CityID

Where LIke c.CityName '%Pune%'

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bharathi Raja8y ago
1. SELECT * FROM employee
2. WHERE CityName LIKE '%Pune%';

Suraj Kumar8y ago

Hi Mohit,

You can do this as below

Create Procedure DynamicSearch

@SearchText varchar(50)

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

Ankit Sharma8y ago

Hi Mohit,

Please try the below update query and let me know if it workd for you or not

set @sqltext= '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+'%'''
exec (@sqltext)

Amit Kumar8y ago

Hi,

Use always single quote '%' in sql not double quote "%" dear.

Mohit Kala8y ago

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+''%''

Mukesh Nayak8y ago

Hi,

We can write the query as following :

SELECT * FROM Employee emp
LEFT OUTER JOIN City city ON city.CityID = emp.CityID
WHERE city.CityName LIKE '%Pune%'

Regards,

Mukesh

Amit Kumar8y ago

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