根据参数更改where子句 [英] change where clause depending on parameter

问题描述

我有：

i have:

ALTER Procedure [dbo].[GetService]
(
@intCountryID int,
@intRegionID int
)

as
Begin
SELECT 
				ECT.ContactID,
				TEC.Country AS [Country Tag],
				TER.Region AS [Region Tag], 
				EBR.BrandCOE AS [BrandCOE Tag],
				ECT.DisplayContact,
				ECT.Contact,
				ECT.Address1,
				ECT.Address2,
				ECT.City,
				EC.Country,
				ER.Region,
				ECT.ContactState,
				ECT.PostalCode,
				ECT.Phone1,
				ECT.Fax,
				ECT.Email,
				ECT.URL

			FROM dbo.Emr_ContactBrandCOE ECBS		

				INNER JOIN dbo.Emr_Contact ECT					ON (ECT.ContactID = ECBS.ContactID)				  
				INNER JOIN dbo.Emr_BrandCOE EBR					ON (ECBS.BrandCOEID = EBR.BrandCOEID)	
				--Contacts Location
				LEFT JOIN dbo.Emr_Country EC					ON (EC.CountryID = ECT.CountryID)
				LEFT JOIN dbo.Emr_Region ER					ON	(ER.RegionID = EC.RegionID)
				--Tag Location
				LEFT JOIN dbo.Emr_Country TEC					ON (TEC.CountryID = ECBS.CountryID)
				LEFT JOIN dbo.Emr_Region TECER					ON	(TECER.RegionID = TEC.RegionID)

				LEFT JOIN dbo.Emr_Region TER					ON (TER.RegionID = ECBS.RegionID)			   
		   WHERE     
				 EBR.BusinessID = 3 
				AND (
						(ECT.CountryID = @intCountryID) OR
						(ECBS.CountryID = @intCountryID) OR
						(ECBS.RegionID = @intRegionID) 
					)

		   ORDER BY ECT.DisplayContact ASC 
end

作为存储过程。我想根据参数改变整个where子句。



如果@intCountryID = 0：

as a stored procedure. I want to change the whole where clause depending on parameter.

if @intCountryID = 0:

WHERE
                 EBR.BusinessID = 3
                AND (
                        (ECBS.RegionID = @intRegionID)
                    )

           ORDER BY ECT.DisplayContact ASC

如果@intCountryID不等于0：

if @intCountryID is not equal to 0:

WHERE
                 EBR.BusinessID = 3
                AND (
                        (ECT.CountryID = @intCountryID) OR
                        (ECBS.CountryID = @intCountryID) OR
                        (ECBS.RegionID = @intRegionID)
                    )

           ORDER BY ECT.DisplayContact ASC

有没有办法实现这个？

is there a way to implement this?

推荐答案

WHERE EBR.BusinessID = 3 
AND 1 = CASE WHEN @intCountryID = 0 
         THEN CASE WHEN ECBS.RegionID = @intRegionID THEN 1 ELSE 0 END
         ELSE 
            CASE WHEN ECT.CountryID = @intCountryID OR ECBS.RegionID = @intRegionID OR ECBS.RegionID = @intRegionID THEN 1 ELSE 0 END
         END
ORDER BY ECT.DisplayContact ASC

快乐编码！

：）

Happy Coding!
:)

怎么样：

How about:

WHERE
    EBR.BusinessID = 3
AND
    (
        (@intCountryID != 0 AND @intCountryID IN (ECT.CountryID, ECBS.CountryID))
    OR
        ECBS.RegionID = @intRegionID
    )

以上答案是正确的。

如果你想放其他解决方案

你可以将所有查询字符串存储到字符串/ varchar / text变量中，并通过put if和put where子句使用连接当你需要

然后

使用exec关键字执行你的查询

above answers are correct.
if you want to put other solution then
you can store all the query string in to string/varchar/ text variable and use concatenation by putting if condition and put where clause as you required
then
execute your query by using exec keyword

这篇关于根据参数更改where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！