Sql Query For Like Operator in Stored Procedure

Question

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

Answer 1

Hi, 

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

 

 

Answer 2

SELECT * FROM employee
WHERE CityName LIKE '%Pune%';

Answer 3

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

 

 

Answer 4

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)

 

 

 

Answer 5

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

 

 

Answer 6

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 

Answer 7

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