Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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
Posted 6-Oct-12 9:20am
Edited 6-Oct-12 9:26am
v2
Comments
Wes Aday at 6-Oct-12 14:24pm
   
You have certainly been a member long enough to know how to ask a question properly. Are we supposed to guess what the error is and where it is coming from?
Sandeep Mewara at 7-Oct-12 1:45am
   
+5!

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
v2
Comments
chander_rani at 5-Nov-12 5:04am
   
Pls tell how to do this search using sql dynamic query, i dont understand pls help to solve this problem
Sandip.Nascar at 16-Nov-12 3:15am
   
I have updated the answer with the corrected sp. Have a look at the issues you have in the sp in the answer section.
damodara naidu betha at 16-Nov-12 8:08am
   
5+

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 8,149
1 Sergey Alexandrovich Kryukov 7,287
2 DamithSL 5,614
3 Manas Bhardwaj 4,986
4 Maciej Los 4,910


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 16 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100