how to search data in stored procedure

set @sql=''
I don't know why you have used two single quotes?

The best way to test any dynamic sql, add the line below just before exec

print(@sql);

In the output window, copy the output text and run in query analyzer.

cheers

Here is your corrected stored procedure.
There are so many issues like double single quote, @keyword datatype error.

alter PROCEDURE [dbo].[usp_GetSearchProperty]  
@keyword bigint,
@Location int,
@purpus int,
@propertyType int,
@plotArea nvarchar(255)  
AS  
BEGIN  
	declare @sql as varchar(max)

	set @sql='select p.*,l.LocationName as locationname,pt.propertyType propertytype,  
	pu.Purpus as purpus from mst_PropertyDetail as p inner join mst_location as l  
	on l.locationId=p.Locationid inner join mst_propertytype as pt   
	on pt.propertytypeid=p.propertytypeid inner join mst_purpus as pu on  
	pu.purpusid=p.purpusid where 1=1'  
	if(@keyword<>'')
	begin
	set @sql=@sql+' and p.propertyNumber like ''%'+  cast(@keyword as varchar)  +'%'' or p.Owner 
	like ''%'+cast(@keyword as varchar)+'%'' or p.demand like ''%'+ cast(@keyword as varchar) +'%'' '
	end 
	if(@plotArea<>'')
	begin
	set @sql=@sql+' and p.PlotArea like ''%'+cast(@keyword as varchar)+'%'' '
	end 
	if(@Location<>'' and @Location<>0)
	begin
	set @sql=@sql+' and p.Locationid='+convert(nvarchar(255),@Location)+''
	end 
	if(@purpus<>'' and @purpus<>0)
	begin
	set @sql=@sql+' and p.purpusid='+convert(nvarchar(255),@purpus)+''
	end 
	if(@propertyType<>'' and @propertyType<>0)
	begin
	set @sql=@sql+' and p.propertytypeid='+convert(nvarchar(255),@propertyType)+''
	end 
	exec(@sql)
	print @sql;
END

cheers