本文介绍了根据参数更改where子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有:
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!
:)
WHERE
EBR.BusinessID = 3
AND
(
(@intCountryID != 0 AND @intCountryID IN (ECT.CountryID, ECBS.CountryID))
OR
ECBS.RegionID = @intRegionID
)
这篇关于根据参数更改where子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!