使用此过程仅当任何参数具有值时才返回数据，如果所有参数都为空，如何使其检索所有数据？ [英] using this procedure it returns data only if any parameter have a value ,, how to make it retrieve all data if all parameters are null ?

问题描述

ALTER PROCEDURE [dbo].[AdvancedSearch]
	@operation nvarchar(max)=null,
	@DonorGender [int] =NULL,
	@NationalityID int =NULL,
	@AgeCatagroyID int =NULL,
	@BehaviorImpression [int] =NULL,
	@CountryID [int] =NULL,
	@EMID [int] =NULL,
	@CityID [int]= NULL,
	@GroupID int =NULL,
	@CategoryID int= NULL,
	@PhoneNumer nvarchar(max)= NULL,
	@DonorName nvarchar(max)= NULL,
	@EmiratesID nvarchar(max)= NULL,
	@BOBox nvarchar(max) =NULL,
	@BOBoxCity int =NULL,
	@Email nvarchar(max) =NULL,
	@MobileNo nvarchar(max)= NULL,
	@Sdate	datetime =null,
	@Edate datetime=null
	
	
AS
BEGIN
	

SET NOCOUNT ON;
	if @operation ='retrieve'
	select [Donor].DonorID, 
	[Donor].[DonorName],
	nationality.[MinorDescriptionAr] Nationality,
	nationality.[MinorDescriptionEn] NationalityEn,
	Gender.[MinorDescriptionAr] Gender,
	Gender.[MinorDescriptionEn] GenderEn,
	[Countries].CountryDescAr,
	[Countries].CountryDescEn,
	[Emirates].EmirateDescAr,
	[Emirates].EmirateDescEn, 
	[Cities].CityDescAr,
	[Cities].CityDescEn
	from 
	Donor
	left join [CommonZakat_MinorLookUpTable] nationality on nationality.MinorID=donor.NationalityID
	left join [CommonZakat_MinorLookUpTable] Gender on Gender.MinorID=donor.DonorGender
	left join [Countries] on Countries.CountryID=donor.CountryID
	left join [Emirates] on Emirates.EmirateID=donor.EMID
	left join[Cities] on  Cities.CityID=Cities.CityID
	left join DonorMobile on DonorMobile.DonorID=donor.DonorID
	where
	([DonorName] like '%'+ @DonorName +'%' and [DonorName] is not null)
      or (DonorMobile.[MobileNo]  like '%'+ @MobileNo +'%'and DonorMobile.[MobileNo] is not null)
      or ([BOBox] like '%'+ @BOBox +'%' and [BOBox] is not null)
	  or( [Email] like '%'+ @Email +'%'and [Email] is not null)
      or([PhoneNumer] like '%'+ @PhoneNumer +'%'and [PhoneNumer] is not null)
      or([DonorDate] between @Sdate And @Edate and [DonorDate] is not null)
      or([BOBoxCity]=@BOBoxCity and [BOBoxCity] is not null)
      or([AgeCatagroyID]=@AgeCatagroyID and [AgeCatagroyID] is not null)
      or([NationalityID]=@NationalityID and [NationalityID] is not null)
      or([EmiratesID] like '%'+ @EmiratesID +'%' and [EmiratesID] is not null)
      or([GroupID]=@GroupID and [GroupID] is not null)
      or([CategoryID]=@CategoryID and [CategoryID] is not null)
      or([BehaviorImpression]=@BehaviorImpression and [BehaviorImpression] is not null)
      or(donor.CountryID=@CountryID and donor.CountryID is not null)
      or([EMID]=@EMID and [EMID] is not null)
      or(Cities.[CityID]=@CityID and Cities.[CityID] is not null)
      or([DonorGender]=@DonorGender and [DonorGender] is not null) 
END

推荐答案

学习使用 ISNULL [ ^ ]。例如，这一行：

Learn to use ISNULL[^] of SQL. For instance this line:

(donor.CountryID=@CountryID and donor.CountryID is not null)

可以这样写：

can be written like this:

(ISNULL(donor.CountryID, -1) in (-1, @CountryID))

如果需要全部返回like参数值的记录为null;检查下面的示例代码

If you need to return all records in case of like parameter value is null; check below sample code

SELECT * FROM MyTable 
WHERE @MyColVal IS NULL OR MyCol LIKE '%'+ @MyColVal+'%'

首先检查参数是否为null，然后应用类似条件。

first check the parameter for null, then apply the like condition.

我刚用（1 = 1）解决了它并检查了参数的无效性





------------------------------------解决方案---------- --------------------------------------

i just solved it using (1=1) and by checking the nullity of the parameters


------------------------------------the solution------------------------------------------------

	where (1=1)
		and ([DonorName] like '%'+ @DonorName +'%' and [DonorName] is not null or @DonorName is null) 
      and (DonorMobile.[MobileNo]  like '%'+ @MobileNo +'%'and DonorMobile.[MobileNo] is not null or @MobileNo is null )
      and ([BOBox] like '%'+ @BOBox +'%' and [BOBox] is not null or @BOBox is null)
	  and ([Email] like '%'+ @Email +'%'and [Email] is not null or @Email is null)
      and ([PhoneNumer] like '%'+ @PhoneNumer +'%'and [PhoneNumer] is not null or @PhoneNumer is null)
      and ([DonorDate] between @Sdate And @Edate and [DonorDate] is not null or @Sdate is null or @Edate is null)
      and ([BOBoxCity]=@BOBoxCity and [BOBoxCity] is not null or @BOBoxCity is null)
      and ([AgeCatagroyID]=@AgeCatagroyID and [AgeCatagroyID] is not null or @AgeCatagroyID is null)
      and ([NationalityID]=@NationalityID and [NationalityID] is not null or @NationalityID is null)
      and ([EmiratesID] like '%'+ @EmiratesID +'%' and [EmiratesID] is not null or @EmiratesID is null)
      and ([GroupID]=@GroupID and [GroupID] is not null or @GroupID is null)
      and ([CategoryID]=@CategoryID and [CategoryID] is not null or @CategoryID is null)
      and ([BehaviorImpression]=@BehaviorImpression and [BehaviorImpression] is not null or @BehaviorImpression is null)
      and (donor.CountryID=@CountryID and donor.CountryID is not null or @CountryID is null)
      and ([EMID]=@EMID and [EMID] is not null or @EMID is null)
      and (Cities.[CityID]=@CityID and Cities.[CityID] is not null or @CityID is null)
      and ([DonorGender]=@DonorGender and [DonorGender] is not null or @DonorGender is null) 
END

这篇关于使用此过程仅当任何参数具有值时才返回数据，如果所有参数都为空，如何使其检索所有数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！