我自己写了一个但是查询出来的条件不是我想要的。小弟是新手希望大家帮帮忙。set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_SearchTMSelect]
(
@TargetID char(10),
@BeginData varchar(50),
@EndData varchar(50),
@WordID varchar(50),
@NameMM varchar(50),
@ProjectID varchar(50),
@TrainNumber varchar(50),
@CompleteSituation varchar(50)
)
as
if exists(select Data from SearchTM where Data = @BeginData)
begin
SELECT GuID, TargetID, Data, WordID, NameMM, Contractor, ProjectID, Target, TrainNumber, CompleteSituation, CertiticateID, TelNuber
FROM SearchTM
WHERE (ProjectID LIKE @ProjectID) OR
(TrainNumber LIKE @TrainNumber) OR
(CompleteSituation LIKE @CompleteSituation) OR
(NameMM LIKE @NameMM) OR
(TargetID LIKE @TargetID) OR
(WordID LIKE @WordID) and
(Data between @BeginData and @EndData)
end
else
begin
SELECT GuID, TargetID, Data, WordID, NameMM, Contractor, ProjectID, Target, TrainNumber, CompleteSituation, CertiticateID, TelNuber
FROM SearchTM
WHERE (ProjectID LIKE @ProjectID) OR
(TrainNumber LIKE @TrainNumber) OR
(CompleteSituation LIKE @CompleteSituation) OR
(NameMM LIKE @NameMM) OR
(TargetID LIKE @TargetID) OR
(WordID LIKE @WordID)
endUSE [NCDBMS]
GODECLARE @return_value intEXEC @return_value = [dbo].[usp_SearchTMSelect]
@TargetID = NULL,
@BeginData = NULL,
@EndData = NULL,
@WordID = NULL,
@NameMM = N'张嘉',
@ProjectID = N'1335870580',
@TrainNumber = NULL,
@CompleteSituation = N'已完成'SELECT 'Return Value' = @return_value结果为
21DEBD85-6CFD-4D10-B3F5-11919C30274B 2008020802 2008-2-8 NO003W 武龙 方好 1335870580 580.00 358/7 未完成 0504554889544 02366445579
220E31B2-1643-44F4-9FB6-48C5BE69226C 2008012801 2008-1-28 NO001W 张嘉 陈雷 1335870580 580.00 358/7 已完成 05011223215456 67215393
4015A12B-DA60-48D8-8002-72CB28848EAF 2008012801 2008-1-28 NO001z 张嘉 陈雷 137340680 680.00 73/4 已完成 05011223215456 67215393
F5242213-A753-4C1B-B122-0946AC5E817E 2008012801 2008-1-28 NO001D 张嘉 陈雷 1310612550 560.00 1061/2 已完成 05011223215456 67215393
只有这条记录才是我想要的,希望各位大哥帮帮我。
220E31B2-1643-44F4-9FB6-48C5BE69226C 2008012801 2008-1-28 NO001W 张嘉 陈雷 1335870580 580.00 358/7 已完成 05011223215456 67215393
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_SearchTMSelect]
(
@TargetID char(10),
@BeginData varchar(50),
@EndData varchar(50),
@WordID varchar(50),
@NameMM varchar(50),
@ProjectID varchar(50),
@TrainNumber varchar(50),
@CompleteSituation varchar(50)
)
as
if exists(select Data from SearchTM where Data = @BeginData)
begin
SELECT GuID, TargetID, Data, WordID, NameMM, Contractor, ProjectID, Target, TrainNumber, CompleteSituation, CertiticateID, TelNuber
FROM SearchTM
WHERE (ProjectID LIKE @ProjectID) OR
(TrainNumber LIKE @TrainNumber) OR
(CompleteSituation LIKE @CompleteSituation) OR
(NameMM LIKE @NameMM) OR
(TargetID LIKE @TargetID) OR
(WordID LIKE @WordID) and
(Data between @BeginData and @EndData)
end
else
begin
SELECT GuID, TargetID, Data, WordID, NameMM, Contractor, ProjectID, Target, TrainNumber, CompleteSituation, CertiticateID, TelNuber
FROM SearchTM
WHERE (ProjectID LIKE @ProjectID) OR
(TrainNumber LIKE @TrainNumber) OR
(CompleteSituation LIKE @CompleteSituation) OR
(NameMM LIKE @NameMM) OR
(TargetID LIKE @TargetID) OR
(WordID LIKE @WordID)
endUSE [NCDBMS]
GODECLARE @return_value intEXEC @return_value = [dbo].[usp_SearchTMSelect]
@TargetID = NULL,
@BeginData = NULL,
@EndData = NULL,
@WordID = NULL,
@NameMM = N'张嘉',
@ProjectID = N'1335870580',
@TrainNumber = NULL,
@CompleteSituation = N'已完成'SELECT 'Return Value' = @return_value结果为
21DEBD85-6CFD-4D10-B3F5-11919C30274B 2008020802 2008-2-8 NO003W 武龙 方好 1335870580 580.00 358/7 未完成 0504554889544 02366445579
220E31B2-1643-44F4-9FB6-48C5BE69226C 2008012801 2008-1-28 NO001W 张嘉 陈雷 1335870580 580.00 358/7 已完成 05011223215456 67215393
4015A12B-DA60-48D8-8002-72CB28848EAF 2008012801 2008-1-28 NO001z 张嘉 陈雷 137340680 680.00 73/4 已完成 05011223215456 67215393
F5242213-A753-4C1B-B122-0946AC5E817E 2008012801 2008-1-28 NO001D 张嘉 陈雷 1310612550 560.00 1061/2 已完成 05011223215456 67215393
只有这条记录才是我想要的,希望各位大哥帮帮我。
220E31B2-1643-44F4-9FB6-48C5BE69226C 2008012801 2008-1-28 NO001W 张嘉 陈雷 1335870580 580.00 358/7 已完成 05011223215456 67215393
TargetID LIKE @TargetID改为
TargetID LIKE '%'+@TargetID+'%'
或
patindex('%'+@TargetID+'%',TargetID)>0
或
charindex(@TargetID,TargetID)>0
220E31B2-1643-44F4-9FB6-48C5BE69226C 2008012801 2008-1-28 NO001W 张嘉 陈雷 1335870580 580.00 358/7 已完成 05011223215456 67215393
------------------------------------------------------------------------- @NameMM = N'张嘉', @ProjectID = N'1335870580',条件应该是 AND 而不是 OR
alter proc [dbo].[usp_SearchTMSelect]
(
@TargetID char(10),
@BeginData varchar(50),
@EndData varchar(50),
@WordID varchar(50),
@NameMM varchar(50),
@ProjectID varchar(50),
@TrainNumber varchar(50),
@CompleteSituation varchar(50)
)
asdeclare @w varchar(8000),@sql varchar(8000)
select @w='',@sql=''if @ProjectID is not null
set @w=@w+'and ProjectID like ''%'+@ProjectID+'%'''if @TrainNumber is not null
set @w=@w+'and TrainNumber like ''%'+@TrainNumber+'%'''if @CompleteSituation is not null
set @w=@w+'and CompleteSituation like ''%'+@CompleteSituation+'%'''if @NameMM is not null
set @w=@w+'and NameMM like ''%'+@NameMM+'%'''if @TargetID is not null
set @w=@w+'and TargetID like ''%'+@TargetID+'%'''if @WordID is not null
set @w=@w+'and WordID like ''%'+@WordID+'%'''if @BeginData is not null and @EndData is not null
set @w=@w+'and Data between '''+@BeginData+''' and '''+@EndData+''''if @w<>'' or @w is not null
set @w=' where '+stuff(@w,1,3,'')
set @sql='select * from SearchTM'+@wprint @sql
--exec (@sql)
exec usp_SearchTMSelect
@TargetID = NULL,
@BeginData = NULL,
@EndData = NULL,
@WordID = NULL,
@NameMM = N'张嘉',
@ProjectID = N'1335870580',
@TrainNumber = NULL,
@CompleteSituation = N'已完成'/*
测试结果:select * from SearchTM where ProjectID like '%1335870580%'and CompleteSituation like '%已完成%'and NameMM like '%张嘉%'*/
下面这个才是可以返回结果集的。alter proc [dbo].[usp_SearchTMSelect]
(
@TargetID char(10),
@BeginData varchar(50),
@EndData varchar(50),
@WordID varchar(50),
@NameMM varchar(50),
@ProjectID varchar(50),
@TrainNumber varchar(50),
@CompleteSituation varchar(50)
)
asdeclare @w varchar(8000),@sql varchar(8000)
select @w='',@sql=''if @ProjectID is not null
set @w=@w+'and ProjectID like ''%'+@ProjectID+'%'''if @TrainNumber is not null
set @w=@w+'and TrainNumber like ''%'+@TrainNumber+'%'''if @CompleteSituation is not null
set @w=@w+'and CompleteSituation like ''%'+@CompleteSituation+'%'''if @NameMM is not null
set @w=@w+'and NameMM like ''%'+@NameMM+'%'''if @TargetID is not null
set @w=@w+'and TargetID like ''%'+@TargetID+'%'''if @WordID is not null
set @w=@w+'and WordID like ''%'+@WordID+'%'''if @BeginData is not null and @EndData is not null
set @w=@w+'and Data between '''+@BeginData+''' and '''+@EndData+''''if @w<>'' or @w is not null
set @w=' where '+stuff(@w,1,3,'')
set @sql='select * from SearchTM'+@wexec (@sql)
比如 判断@BeginDate时 还要加个条件isdate(@beginDate)=1具体的根据你的情况,自己完善,我已经给你一个思路了!
改成这样就可以了!if isdate(@BeginData)=1 and isdate(@EndData)=1
set @w=@w+'and Data between '''+@BeginData+''' and '''+@EndData+''''还有日期应该是 DATE 而不是 DATA
@BeginData 应该是 @BeginDate