My procedure for database

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE proc dbo.sp_searchdata_vehicle_listing

--sp_searchdata_vehicle_listing 'Bihar', 1

@input varchar(500),

@way_type varchar(100),

@distance_input varchar(100)

as

begin

declare @show_price decimal(18,2),@distance int

if(@distance_input<>'')

 begin

   set @distance=cast(ltrim(rtrim(replace((replace(@distance_input,',','')),'km',''))) as int)

   if(@way_type='Return')

   begin

select (((isnull(base_fare,0)*@distance)+((isnull(base_fare,0)*@distance)*isnull(GST_rate,0)/100 ))*2) as showPrice, STUFF((SELECT ' | ' + vehicle_fature_name FROM tbl_Vehicle_faeture WITH(NOWAIT,NOLOCK) WHERE is_active=1 AND 

id IN (SELECT vechicle_feature_id FROM VehicleFeature_for_user WITH(NOWAIT,NOLOCK) where vechicle_listing_Id=vl.id )  FOR XML PATH('')), 1, 2, '') AS car_feature,vl.* 

from Vehicle_listing vl WITH(NOWAIT,NOLOCK)

where --CONTAINS(vl.Location_Covered,'@input'')

 

vl.Location_Covered like '%'+@input+'%'or vl.Location_Covered like ''+@input+'%'

or vl.Location_Covered like ''+@input+''or vl.Location_Covered like '%'+@input+'' 

or vl.Location_Covered=''+@input+''

--or vl.Location_Covered like '_'+@input+'%' 

--or vl.Location_Covered like '['+@input+']' 

--or vl.Location_Covered CONTAINS ''+@input+''

end

else if(@way_type='One Way')

begin

       select ((isnull(base_fare,0)*@distance)+((isnull(base_fare,0)*@distance)*isnull(GST_rate,0)/100 )) as showPrice, STUFF((SELECT ' | ' + vehicle_fature_name FROM tbl_Vehicle_faeture WITH(NOWAIT,NOLOCK) WHERE is_active=1 AND 

id IN (SELECT vechicle_feature_id FROM VehicleFeature_for_user WITH(NOWAIT,NOLOCK) where vechicle_listing_Id=vl.id )  FOR XML PATH('')), 1, 2, '') AS car_feature,vl.* 

from Vehicle_listing vl WITH(NOWAIT,NOLOCK)

where --CONTAINS(vl.Location_Covered,'@input'')

 

vl.Location_Covered like '%'+@input+'%'or vl.Location_Covered like ''+@input+'%'

or vl.Location_Covered like ''+@input+''or vl.Location_Covered like '%'+@input+'' 

or vl.Location_Covered=''+@input+''

  end

end

else

begin

 

      if(@way_type='Return')

   begin

select (((isnull(base_fare,0)*@distance)+((isnull(base_fare,0)*@distance)*isnull(GST_rate,0)/100 ))*2) as showPrice, STUFF((SELECT ' | ' + vehicle_fature_name FROM tbl_Vehicle_faeture WITH(NOWAIT,NOLOCK) WHERE is_active=1 AND 

id IN (SELECT vechicle_feature_id FROM VehicleFeature_for_user WITH(NOWAIT,NOLOCK) where vechicle_listing_Id=vl.id )  FOR XML PATH('')), 1, 2, '') AS car_feature,vl.* 

from Vehicle_listing vl WITH(NOWAIT,NOLOCK)

where --CONTAINS(vl.Location_Covered,'@input'')

 

vl.Location_Covered like '%'+@input+'%'or vl.Location_Covered like ''+@input+'%'

or vl.Location_Covered like ''+@input+''or vl.Location_Covered like '%'+@input+'' 

or vl.Location_Covered=''+@input+''

--or vl.Location_Covered like '_'+@input+'%' 

--or vl.Location_Covered like '['+@input+']' 

--or vl.Location_Covered CONTAINS ''+@input+''

end

else if(@way_type='One Way')

begin

       select ((isnull(base_fare,0)*@distance)+((isnull(base_fare,0)*@distance)*isnull(GST_rate,0)/100 )) as showPrice, STUFF((SELECT ' | ' + vehicle_fature_name FROM tbl_Vehicle_faeture WITH(NOWAIT,NOLOCK) WHERE is_active=1 AND 

id IN (SELECT vechicle_feature_id FROM VehicleFeature_for_user WITH(NOWAIT,NOLOCK) where vechicle_listing_Id=vl.id )  FOR XML PATH('')), 1, 2, '') AS car_feature,vl.* 

from Vehicle_listing vl WITH(NOWAIT,NOLOCK)

where --CONTAINS(vl.Location_Covered,'@input'')

 

vl.Location_Covered like '%'+@input+'%'or vl.Location_Covered like ''+@input+'%'

or vl.Location_Covered like ''+@input+''or vl.Location_Covered like '%'+@input+'' 

or vl.Location_Covered=''+@input+''

  end

end

end