how to search data in stored procedure

Pls some one tell that how to search data by stored procedure i have write code but it throws error. pls help me to correct my procedure is

CREATE 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 ''''%''+@keyword+''%'''' or p.Owner 
like ''''%''+@keyword+''%'''' or p.demand like ''''%''+@keyword+''%'''' ''
end 
if(@plotArea<>'''')
begin
set @sql=@sql+'' and p.PlotArea like ''''%''+@keyword+''%'''' ''
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)
END

Tags: SQL Server 2005, SQL Server