How to search multiple values in a single column using stored procedure

Create Procedure

create procedure sp_search(@lnames AS varchar(100))

as

begin

SELECT * FROM tbl_Locations

WHERE CHARINDEX(',' + locationName + ',', ',' + @lnames + ',') > 0

end

Execute:

exec sp_search 'Govindapura,Basaveshwara Nagar,Bhadranna Layout'