Tricky where clause

Question

I don’t know if it’s me being on the back end of a very long day or I’ve got the coders equivalent of writers block but I can’t think of a clean way of doing this.

I have a table that stores a web pages menu structure, and i want a simple stored procedure that will return the relevant menu items based on the session parameters in the web application.

Take the following (simplified) example:

--#### Create example table
CREATE TABLE [dbo].[tbl_Page](
    [PageID] [int] IDENTITY(1,1) NOT NULL,
    [RequireLogin] [bit] NOT NULL,
    [RequireAdmin] [bit] NOT NULL,
    [HideIfLoggedIn] [bit] NOT NULL
)
GO

--#### Insert Dummy Data
SET IDENTITY_INSERT [dbo].[tbl_Page] ON
INSERT [dbo].[tbl_Page] ([PageID], [RequireLogin], [RequireAdmin], [HideIfLoggedIn]) VALUES (2, 1, 0, 0)
INSERT [dbo].[tbl_Page] ([PageID], [RequireLogin], [RequireAdmin], [HideIfLoggedIn]) VALUES (3, 1, 1, 0)
INSERT [dbo].[tbl_Page] ([PageID], [RequireLogin], [RequireAdmin], [HideIfLoggedIn]) VALUES (4, 0, 0, 1)
INSERT [dbo].[tbl_Page] ([PageID], [RequireLogin], [RequireAdmin], [HideIfLoggedIn]) VALUES (5, 0, 0, 0)
SET IDENTITY_INSERT [dbo].[tbl_Page] OFF

--#### Create menu procedure
CREATE PROCEDURE usp_GetSubMenu
    @ParentID INT ,
    @IsLoggedIn BIT ,
    @IsAdmin BIT
AS 
    BEGIN
        SET NOCOUNT ON;

        SELECT  PageID ,
                RequireLogin ,
                RequireAdmin ,
                HideIfLoggedIn
        FROM    tbl_Page
        WHERE ????????????
    END
GO

For the given example data, the following needs to be true:

1. Page ID 2 should only be listed if @IsLoggedIn = 1
2. Page ID 3 should only be listed if @IsLoggedIn = 1 AND @IsAdmin = 1
3. Page ID 4 should be listed all the time UNLESS @IsLoggedIn = 1 (there may well be pages that require a logged in user (and even an admin user) but still need hiding too - this is the bit where my brain explodes...)
4. Page ID 5 should be visible all the time regardless if the user is logged in or not and if they are an admin or not as it does not require login and it isnt hidden for logged in users

Answer 1

select PageID,
  RequireLogin,
  RequireAdmin,
  HideIfLoggedIn
from tbl_Page
where (HideIfLoggedIn <> @IsLoggedIn)
  and (RequireLogin = 0 or @IsLoggedIn = 1)
  and (RequireAdmin = 0 or @IsAdmin = 1)

Works for me.

Edit after comment:

select PageID,
  RequireLogin,
  RequireAdmin,
  HideIfLoggedIn
from tbl_Page
where ((HideIfLoggedIn <> @IsLoggedIn)
  and (RequireLogin = 0 or @IsLoggedIn = 1)
  and (RequireAdmin = 0 or @IsAdmin = 1))
  or (RequireLogin = 0 and RequireAdmin = 0 and HideIfLoggedIn = 0)

I've updated the query to catch your last requirement, and updated the following SQL Fiddles:

SQL Fiddle for @IsLoggedIn = 1, IsAdmin = 0 - PageID 2,5 displayed.

SQL Fiddle for @IsLoggedIn = 1, IsAdmin = 1 - PageID 2,3,5 displayed.

SQL Fiddle for @IsLoggedIn = 0, IsAdmin = 1 - PageID 4,5 displayed.