CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
HeavenCore
HeavenCore

Reputation: 7683

Tricky where clause

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

Upvotes: 3

Views: 179

Answers (3)

Aheho
Aheho

Reputation: 12821

I think this has better readability when the where clause is structured around the negative...

SELECT 
    PageID,
    RequireLogin,
    RequireAdmin,
    HideIfLoggedIn
FROM 
    tbl_Page
WHERE 
    NOT
    (
       (HideIfLoggedIn = 1 AND @IsLoggedIn = 1)
       OR (RequireLogin = 1 AND @IsLoggedIn = 0)
       OR (RequireAdmin = 1 AND @IsAdmin = 0)
    )

Upvotes: 1

Ian Preston
Ian Preston

Reputation: 39566

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.

Upvotes: 2

pero
pero

Reputation: 4259

SELECT  PageID ,
            RequireLogin ,
            RequireAdmin ,
            HideIfLoggedIn
    FROM    tbl_Page
    WHERE 
   (@IsLoggedIn = 1 AND [HideIfLoggedIn] = 0) 
   AND [RequireLogin] = @IsLoggedIn
   AND [RequireAdmin] = @IsAdmin

Upvotes: 1

Related Questions