小弟最近写一个多条件模糊查询视图的存储过程,可是只要条件超过两个就出现异常,还请各位高手帮忙,以下是代码.
CREATE PROCEDURE DSearchEnterPrise@Oname nvarchar(50),
@Address nvarchar(50),
@Xueli nvarchar(50),
@Special nvarchar(50),
@Sex nvarchar(10),
@Salary nvarchar(20),
@Language nvarchar(10)
AS
declare @StrSql nvarchar(100)
set @StrSql='select * from EnterPriseInfoView where (1=1)'
if(@Oname<>'')
begin
set @StrSql=@StrSql+' AND (Oname like ''%'+@Oname+'%'')'
end
if(@Address<>'')
begin
set @StrSql=@StrSql+' AND (Eaddress like ''%'+@Address+'%'')'
end
if(@Xueli<>'')
begin
set @StrSql=@StrSql+' AND (Oxueli like ''%'+@Xueli+'%'')'
end
if(@Special<>'')
begin
set @StrSql=@StrSql+' AND (Ospecial like ''%'+@Special+'%'')'
end
set @StrSql=@StrSql+' AND Osex='''+@Sex+''''set @StrSql=@StrSql+' AND Osalary='''+@salary+''''if(@Language<>'')
begin
set @StrSql=@StrSql+' AND (Odemand like ''%'+@Language+'%'')'
end
exec(@StrSql)
GO
CREATE PROCEDURE DSearchEnterPrise@Oname nvarchar(50),
@Address nvarchar(50),
@Xueli nvarchar(50),
@Special nvarchar(50),
@Sex nvarchar(10),
@Salary nvarchar(20),
@Language nvarchar(10)
AS
declare @StrSql nvarchar(100)
set @StrSql='select * from EnterPriseInfoView where (1=1)'
if(@Oname<>'')
begin
set @StrSql=@StrSql+' AND (Oname like ''%'+@Oname+'%'')'
end
if(@Address<>'')
begin
set @StrSql=@StrSql+' AND (Eaddress like ''%'+@Address+'%'')'
end
if(@Xueli<>'')
begin
set @StrSql=@StrSql+' AND (Oxueli like ''%'+@Xueli+'%'')'
end
if(@Special<>'')
begin
set @StrSql=@StrSql+' AND (Ospecial like ''%'+@Special+'%'')'
end
set @StrSql=@StrSql+' AND Osex='''+@Sex+''''set @StrSql=@StrSql+' AND Osalary='''+@salary+''''if(@Language<>'')
begin
set @StrSql=@StrSql+' AND (Odemand like ''%'+@Language+'%'')'
end
exec(@StrSql)
GO
解决方案 »
- 问个技术很高深的问题
- winfrom程序如何向弹出的模式窗口传参?
- 搜索框下拉框的问题
- 本地打开网页正常 iis或vs调试打开网页 js无效!
- MS:TreeView显示成字符串的问题
- .net 2.0不能映射根目录?
- 一个HTML文件转换成DOC文件的问题,在windows下面是好的,但在asp.net中有问题,请大家帮看看
- 快帮我看看更新数据的问题
- 请问联众游戏的构架
- 各位,请教哪儿有asp.net的web自定义控件的开发的好的资料介绍吗?
- DataGrid的选择按钮的onclick事件调用的javascript程序中的window.setTimeout没有得到执行。特此请教。多谢了!!!
- 一个超级菜鸟问题,怎样取text的值。
你在exec(@StrSql) 这句之前加入这句:print @StrSql 或则select @StrSql
然后你把这个输出的值copy出来单独执行看看是语法上什么地方出了问题.
嗨!兄弟,如果我的回答解决了你的问题,记得给我点分哦.我刚刚注册进来的.谢了!
create proc dbo.sp_AAA
@type nvarchar(20),
@name nvarchar(20)
as
if @type='' set @type=null
if @name='' set @name=nullselect * from tb_AAA
where type like '%'+isnull(@type,type)+'%'
and name like '%'+isnull(@name,name)+'%'条件再多都可以用这种方法,其它类型参数方法也类似。接分了。
@caseId varchar(50) = null,
@state int = -1,
@type int = -1,
@projectId varchar(50) = null,
@projectName varchar(200) = null,
@slr varchar(50) = null,
@ffr varchar(50) = null,
@begin smalldatetime = null,
@end smalldatetime = null,
@taskName varchar(200) = null
AS
SELECT distinct task.*, user1.name AS ffr_name, user1.mobile AS ffr_mobile,
taskState.taskStateName, taskType.taskTypeName, user1.email AS ffr_email,
user2.mobile AS slr_mobile, user2.email AS slr_email,user2.name AS slr_name
FROM tsc_task_web_taskSend task INNER JOIN
tsc_task_web_InnerUserInfo user1 ON task.ffr = user1.userName INNER JOIN
tsc_task_web_InnerUserInfo user2 ON task.slr = user2.userName INNER JOIN
tsc_task_web_taskState taskState ON task.state = taskState.taskStateId INNER JOIN
tsc_task_web_taskType taskType ON taskType.taskTypeId = task.caseTypeId
where ( @projectId is null or projectId = @projectId) and (@caseId is null or caseId = @caseId) and
(@slr is null or slr = @slr or ( ffr = @slr and @slr is not null ) ) and (@ffr is null or ffr = @ffr) and
(@projectName is null or projectName like @projectName)
and (@begin is null or createTime >= @begin) and (@end is null or createTime <= @end) and
(@state = -1 or state = @state ) and (@type = -1 or casetypeId = @type) and (@taskName is null or taskName like @taskName)
and (task.delFlag = 0 or task.delFlag is null)
GO