SQL与Case语句的where子句重置价值

问题描述：

的形式和查询的一个多次查询，我需要Case语句的where子句，使支票价值@CategoryID我的条件如果@CategoryID <> 0那么@CategoryID = 0

注意：我不能通过@CategoryID =0作为联合声明中的一个需要实际值。

我需要将@CategoryID仅重置为类别ID的情况下最后的陈述为0不为0

我试着用在where子句也产生错误，如果产生错误&声明我也试过case语句。我希望帮助在这方面，这样我可以为过去的SQL语句重置@CategoryID =0在存储过程

ALTER PROCEDURE [dbo].[usp_GetBannerByIDsAPICPL] 
    @ArticleID int, 
    @PageID int, 
    @IssueID int, 
    @CategoryID int, 
    @BannerLayoutPosition int, 
    @LangID int 
AS 
BEGIN 
    SET NOCOUNT ON 

    -- will show artile related banner 
    SELECT BannerID, BannerName, '../images/Banners/' +BannerImageFile AS ImagePath,BannerURL,BannerLocation FROM Banner_Manager  
    WHERE ArticleID = @ArticleID AND BannerLocation = @BannerLayoutPosition AND [email protected] AND Active = 1 
    UNION ALL 

    -- show banner by category & Issue 
    SELECT BannerID, BannerName, '../images/Banners/' +BannerImageFile AS ImagePath ,BannerURL,BannerLocation FROM Banner_Manager 
    WHERE CategoryID = @CategoryID AND IssueID = @IssueID AND BannerLocation = @BannerLayoutPosition 
    AND [email protected] AND Active = 1 
     UNION ALL 

    -- show banner by category 
    SELECT BannerID, BannerName, '../images/Banners/' +BannerImageFile AS ImagePath ,BannerURL,BannerLocation FROM Banner_Manager 
    WHERE CategoryID = @CategoryID AND BannerLocation = @BannerLayoutPosition 
    AND [email protected] AND Active = 1 
    UNION ALL 
    -- will show page related banner 
    SELECT BannerID, BannerName, '../images/Banners/' +BannerImageFile AS ImagePath,BannerURL,BannerLocation FROM Banner_Manager  
    WHERE PageID = @PageID AND BannerLocation = @BannerLayoutPosition AND [email protected] AND Active = 1 
    UNION ALL 
    --will show issue related banner 
    -- Need To Check if @CategoryID is not 0 if it is not 0 then i have to set the @CategoryID = 0 
    SELECT BannerID, BannerName, '../images/Banners/' +BannerImageFile AS ImagePath,BannerURL,BannerLocation FROM Banner_Manager  
    WHERE IssueID = @IssueID AND CategoryID = @CategoryID AND BannerLocation = @BannerLayoutPosition AND [email protected] AND Active = 1 


END

答

如果这种情况只适用于最后一条语句：

@CategoryID <> 0 then @CategoryID = 0

的你为什么当您要检查的值始终为零时，仍然将参数传递给@CategoryID？

在最后一条语句，为什么你不直接与零比较类别：

-- will show issue related banner 
SELECT BannerID, 
     BannerName, 
     '../images/Banners/' + BannerImageFile AS ImagePath, 
     BannerURL, 
     BannerLocation 
FROM Banner_Manager  
WHERE IssueID = @IssueID AND 
     CategoryID = 0 AND  -- <<== HERE 
     BannerLocation = @BannerLayoutPosition AND 
     [email protected] AND 
     Active = 1

答

不要试图在@CategoryID在union语句设置为0，如果你不希望它在不改变该查询。在单独的声明中将其设置为0：

UNION ALL 
--will show issue related banner 
-- Need To Check if @CategoryID is not 0 if it is not 0 then i have to set the @CategoryID = 0 
SELECT BannerID, BannerName, '../images/Banners/' +BannerImageFile AS ImagePath,BannerURL,BannerLocation FROM Banner_Manager  
WHERE IssueID = @IssueID AND CategoryID = @CategoryID AND BannerLocation = @BannerLayoutPosition AND [email protected] AND Active = 1 

SET @CategoryID = 0

SQL与Case语句的where子句重置价值

相关推荐