如何在存储过程中搜索数据 [英] 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

推荐答案

set @ sql =''''''
我不知道您为什么使用两个单引号?

测试任何动态sql的最佳方法，在exec
之前添加以下行

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);

在输出窗口中，复制输出文本并在查询分析器中运行.

欢呼

这是您更正的存储过程.
有很多问题，例如双引号，@ keyword数据类型错误.

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

这篇关于如何在存储过程中搜索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！