多条件模糊查询问题

Question

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[USP_GetFamilyInfo]
-- Add the parameters for the stored procedure here
@FM_FamilyAddress varchar(200),
@FM_Host varchar(50),
@FM_ArchivesNumber varchar(50),
@FM_FamilyTel varchar(50),
@StartPageIndex int,
@EndPageIndex int,
@countPage int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Create table #tmpdWS_FamilyManage(
RowID int,
FM_ID int,
FM_ArchivesNumber varchar(200),
FM_Host varchar(50),
FM_FamilyAddress varchar(200),
FM_FamilyTel varchar(50),
FM_PeopleCount int,
FM_CurrentCount int,
FM_LiveSize varchar(50),
FM_Status bit,
FM_AddDate datetime
)
    -- Insert statements for procedure here
Declare @sqlSelect varchar(1000),@sqlWhere varchar(1000)
set @sqlSelect='select * into #tmpdWS_FamilyManage from(
select ROW_NUMBER() over (order by FM_ID desc) as RowID,FM_ID,FM_ArchivesNumber,FM_Host,
FM_FamilyAddress,FM_FamilyTel,FM_PeopleCount,FM_CurrentCount,FM_LiveSize,FM_Status,FM_AddDate from WS_FamilyManage
 where 1=1'
set @sqlWhere=case when @FM_FamilyAddress<>'' then ' or FM_FamilyAddress like ''%'+@FM_FamilyAddress+'%''' else N'' end
set @sqlWhere=@sqlWhere+case when @FM_Host<>'' then N' or FM_Host like ''%'+@FM_Host+'%''' else N'' end
set @sqlWhere=@sqlWhere+case when @FM_ArchivesNumber<>'' then N' or FM_ArchivesNumber like ''%'+@FM_ArchivesNumber+'%''' else N'' end
set @sqlWhere=@sqlWhere+case when @FM_FamilyTel<>'' then N' or FM_FamilyTel like ''%'+@FM_FamilyTel+'%''' else N'' end
set @sqlSelect=@sqlSelect+@sqlWhere+') AS A'
exec(@sqlSelect)--execute sp_Executesql @sqlSelect
--print @sqlSelect
select @countPage=count(RowID) from #tmpdWS_FamilyManage
print @countPage
--select * from #tmpdWS_FamilyManage
-- select FM_ID,FM_ArchivesNumber,FM_Host,
--FM_FamilyAddress,FM_FamilyTel,FM_PeopleCount,FM_CurrentCount,FM_LiveSize,FM_Status,FM_AddDate from #tmpdWS_FamilyManage where RowID between @StartPageIndex and @EndPageIndex
drop table #tmpdWS_FamilyManageENDexec USP_GetFamilyInfo '','','','',1,10,100
print @countPage--->0
执行了这条语句，临时表中没有记录。为什么呢？

Answer 1

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[USP_GetFamilyInfo]
    -- Add the parameters for the stored procedure here
    @FM_FamilyAddress varchar(200),
    @FM_Host varchar(50),
    @FM_ArchivesNumber varchar(50),
    @FM_FamilyTel varchar(50),
    @StartPageIndex int,
    @EndPageIndex int,
    @countPage int output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
Create table #tmpdWS_FamilyManage(
    RowID int,
    FM_ID int,
    FM_ArchivesNumber varchar(200),
    FM_Host varchar(50),
    FM_FamilyAddress varchar(200),
    FM_FamilyTel varchar(50),
    FM_PeopleCount int,
    FM_CurrentCount int,
    FM_LiveSize varchar(50),
    FM_Status bit,
    FM_AddDate datetime
)
    -- Insert statements for procedure here
    Declare @sqlSelect varchar(1000),@sqlWhere varchar(1000)
    set @sqlSelect='Insert into #tmpdWS_FamilyManage select * from(
select ROW_NUMBER() over (order by FM_ID desc) as RowID,FM_ID,FM_ArchivesNumber,FM_Host,
FM_FamilyAddress,FM_FamilyTel,FM_PeopleCount,FM_CurrentCount,FM_LiveSize,FM_Status,FM_AddDate from WS_FamilyManage
 where 1=1'
    set @sqlWhere=case when @FM_FamilyAddress<>'' then ' or FM_FamilyAddress like ''%'+@FM_FamilyAddress+'%''' else N'' end
    set @sqlWhere=@sqlWhere+case when @FM_Host<>'' then N' or FM_Host like ''%'+@FM_Host+'%''' else N'' end
    set @sqlWhere=@sqlWhere+case when @FM_ArchivesNumber<>'' then N' or FM_ArchivesNumber like ''%'+@FM_ArchivesNumber+'%''' else N'' end
    set @sqlWhere=@sqlWhere+case when @FM_FamilyTel<>'' then N' or FM_FamilyTel like ''%'+@FM_FamilyTel+'%''' else N'' end
    set @sqlSelect=@sqlSelect+@sqlWhere+') AS A'
    exec(@sqlSelect)--execute sp_Executesql @sqlSelect
    --print @sqlSelect
    select @countPage=count(RowID) from #tmpdWS_FamilyManage
    print @countPage
    select * from #tmpdWS_FamilyManage
--    select FM_ID,FM_ArchivesNumber,FM_Host,
--FM_FamilyAddress,FM_FamilyTel,FM_PeopleCount,FM_CurrentCount,FM_LiveSize,FM_Status,FM_AddDate from #tmpdWS_FamilyManage where RowID between @StartPageIndex and @EndPageIndex
drop table #tmpdWS_FamilyManageEND

Answer 2

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[USP_GetFamilyInfo]
-- Add the parameters for the stored procedure here
@FM_FamilyAddress varchar(200),
@FM_Host varchar(50),
@FM_ArchivesNumber varchar(50),
@FM_FamilyTel varchar(50),
@StartPageIndex int,
@EndPageIndex int,
@countPage int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Create table #tmpdWS_FamilyManage(
RowID int,
FM_ID int,
FM_ArchivesNumber varchar(200),
FM_Host varchar(50),
FM_FamilyAddress varchar(200),
FM_FamilyTel varchar(50),
FM_PeopleCount int,
FM_CurrentCount int,
FM_LiveSize varchar(50),
FM_Status bit,
FM_AddDate datetime
)
    -- Insert statements for procedure here
Declare @sqlSelect varchar(1000),@sqlWhere varchar(1000)
set @sqlSelect='Insert into #tmpdWS_FamilyManage select * from(
select ROW_NUMBER() over (order by FM_ID desc) as RowID,FM_ID,FM_ArchivesNumber,FM_Host,
FM_FamilyAddress,FM_FamilyTel,FM_PeopleCount,FM_CurrentCount,FM_LiveSize,FM_Status,FM_AddDate from WS_FamilyManage
 where 1=1'
set @sqlWhere=case when @FM_FamilyAddress<>'' then ' or FM_FamilyAddress like ''%'+@FM_FamilyAddress+'%''' else '' end
set @sqlWhere=@sqlWhere+case when @FM_Host<>'' then ' or FM_Host like ''%'+@FM_Host+'%''' else '' end
set @sqlWhere=@sqlWhere+case when @FM_ArchivesNumber<>'' then ' or FM_ArchivesNumber like ''%'+@FM_ArchivesNumber+'%''' else '' end
set @sqlWhere=@sqlWhere+case when @FM_FamilyTel<>'' then ' or FM_FamilyTel like ''%'+@FM_FamilyTel+'%''' else '' end
set @sqlSelect=@sqlSelect+@sqlWhere+') AS A'
exec(@sqlSelect)
--print @sqlSelect
select @countPage=count(RowID) from #tmpdWS_FamilyManage
print @countPage select FM_ID,FM_ArchivesNumber,FM_Host,
FM_FamilyAddress,FM_FamilyTel,FM_PeopleCount,FM_CurrentCount,FM_LiveSize,FM_Status,FM_AddDate from #tmpdWS_FamilyManage where RowID between @StartPageIndex and @EndPageIndex
drop table #tmpdWS_FamilyManageEND原来如此，哈哈 多谢楼上的兄弟