如何修复spRecipe_GetAll存储过程以正确筛选指定类别及用户食谱

Got it, let's tackle this problem. The root cause here is incorrect logical operator precedence and missing category filtering for the user's inactive recipes in your WHERE clauses. Right now, when you filter by CategoryID, the OR userid=@userid condition is overriding the category check for your own inactive recipes—so it's pulling all your inactive recipes regardless of their category, which isn't what you want.

What Needs to Change #

For any branch where you're filtering by @CategoryID > 0, you need to ensure:

1. Active recipes match the specified category
2. Your own inactive recipes also match the specified category
3. Use parentheses to group logical conditions correctly (since AND has higher precedence than OR, we need to explicitly group the two valid cases)

Modified Stored Procedure Code #

ALTER procedure [dbo].[spRecipe_GetAll] 
    @userid int, 
    @RecipeName varchar(250), 
    @CategoryID int 
as 
begin 
    set nocount on; 

    -- Case 1: No search terms, no category filter
    IF (@RecipeName = '' and @CategoryID = 0) 
    BEGIN 
        SELECT distinct 
            def_Recipes.ThumbImageUrl, 
            def_Category.CategoryName, 
            def_Recipes.RecipeID, 
            def_Recipes.Name, 
            def_Recipes.Description, 
            def_Recipes.RecipeFrom, 
            def_Recipes.RecipeYield, 
            def_Recipes.CookingTime, 
            def_Recipes.PrepTime, 
            def_Recipes.ReadyTime, 
            def_Recipes.IsActive, 
            case when def_Recipes.IsActive =0 then 'DeActive' else 'Active' end as isactive, 
            case when def_Recipes.IsApproved ='1' then 'Approved' 
                 when def_Recipes.IsApproved ='0' then 'Declined' 
                 else 'Pending' end as IsApproved 
        FROM dbo.def_Recipes 
        INNER JOIN def_Category ON def_Recipes.CategoryID = def_Category.CategoryID 
        WHERE (def_Recipes.IsActive='1') OR (userid=@userid and def_Recipes.IsActive='0')
    End 

    -- Case 2: Search by recipe name, no category filter
    IF (@RecipeName != '' and @CategoryID = 0) 
    BEGIN 
        SELECT distinct 
            def_Recipes.ThumbImageUrl, 
            def_Category.CategoryName, 
            def_Recipes.RecipeID, 
            def_Recipes.Name, 
            def_Recipes.Description, 
            def_Recipes.RecipeFrom, 
            def_Recipes.RecipeYield, 
            def_Recipes.CookingTime, 
            def_Recipes.PrepTime, 
            def_Recipes.ReadyTime, 
            case when def_Recipes.IsActive =0 then 'DeActive' else 'Active' end as isactive, 
            case when def_Recipes.IsApproved ='1' then 'Approved' 
                 when def_Recipes.IsApproved ='0' then 'Declined' 
                 else 'Pending' end as IsApproved 
        FROM dbo.def_Recipes 
        INNER JOIN def_Category ON def_Recipes.CategoryID = def_Category.CategoryID 
        WHERE (def_Recipes.Name = @RecipeName AND def_Recipes.IsActive='1') 
               OR (userid=@userid AND def_Recipes.Name = @RecipeName AND def_Recipes.IsActive='0')
    End 

    -- Case 3: Filter by category, no recipe name search
    IF (@RecipeName = '' and @CategoryID > 0) 
    BEGIN 
        SELECT distinct 
            def_Recipes.ThumbImageUrl, 
            def_Category.CategoryName, 
            def_Recipes.RecipeID, 
            def_Recipes.Name, 
            def_Recipes.Description, 
            def_Recipes.RecipeFrom, 
            def_Recipes.RecipeYield, 
            def_Recipes.CookingTime, 
            def_Recipes.PrepTime, 
            def_Recipes.ReadyTime, 
            case when def_Recipes.IsActive =0 then 'DeActive' else 'Active' end as isactive, 
            case when def_Recipes.IsApproved ='1' then 'Approved' 
                 when def_Recipes.IsApproved ='0' then 'Declined' 
                 else 'Pending' end as IsApproved 
        FROM dbo.def_Recipes 
        INNER JOIN def_Category ON def_Recipes.CategoryID = def_Category.CategoryID 
        WHERE (def_Recipes.CategoryID = @CategoryID AND def_Recipes.IsActive='1') 
               OR (userid=@userid AND def_Recipes.IsActive='0' AND def_Recipes.CategoryID = @CategoryID)
    End 

    -- Case 4: Filter by both category and recipe name
    IF (@RecipeName != '' and @CategoryID > 0) 
    BEGIN 
        SELECT distinct 
            def_Recipes.ThumbImageUrl, 
            def_Category.CategoryName, 
            def_Recipes.RecipeID, 
            def_Recipes.Name, 
            def_Recipes.Description, 
            def_Recipes.RecipeFrom, 
            def_Recipes.RecipeYield, 
            def_Recipes.CookingTime, 
            def_Recipes.PrepTime, 
            def_Recipes.ReadyTime, 
            case when def_Recipes.IsActive =0 then 'DeActive' else 'Active' end as isactive, 
            case when def_Recipes.IsApproved ='1' then 'Approved' 
                 when def_Recipes.IsApproved ='0' then 'Declined' 
                 else 'Pending' end as IsApproved 
        FROM dbo.def_Recipes 
        INNER JOIN def_Category ON def_Recipes.CategoryID = def_Category.CategoryID 
        WHERE (def_Recipes.CategoryID = @CategoryID AND def_Recipes.Name = @RecipeName AND def_Recipes.IsActive='1') 
               OR (userid=@userid AND def_Recipes.IsActive='0' AND def_Recipes.CategoryID = @CategoryID AND def_Recipes.Name = @RecipeName)
    End 
end

Key Fixes Explained #

• For category-filtered cases (3 and 4), we added def_Recipes.CategoryID = @CategoryID to the user's inactive recipe condition, ensuring only your own recipes in the specified category are returned.
• Wrapped each valid condition group in parentheses to avoid precedence issues—this makes sure the logic is exactly what we want: either (active + category match) OR (your inactive + category match).
• Also cleaned up the other cases to follow the same grouping pattern for consistency, so even when searching by name without category, it only returns name-matching recipes (active ones, plus your own inactive name-matching ones).

内容的提问来源于stack exchange，提问作者Utron Developer