本文介绍了如何在存储过程中搜索数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

有人告诉我如何通过存储过程搜索数据,我已经写了代码,但是会抛出错误.请帮助我纠正我的程序是

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


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

08-22 19:47
查看更多