本文介绍了如何在存储过程中搜索数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
有人告诉我如何通过存储过程搜索数据,我已经写了代码,但是会抛出错误.请帮助我纠正我的程序是
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
推荐答案
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
这篇关于如何在存储过程中搜索数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!