October 4, 2016 at 3:25 pm
Hello,
I'm trying to accomplish creating a sql where clause based on a person's security setting in the database. Below is the query I'm trying to get to work. I created a stored procedure to dynamically create the query, but I would really like to try to get this to work.
SELECT n.*
FROM FirmNotes n
WHERE n.Active = 1
ANDn.EffDt = ( SELECT MAX(n1.EffDt) From FirmNotes n1
WHERE n1.NoteID = n.NoteID
AND n1.EffDt <= GETDATE() )
SELECT CASE
WHEN r1.RoleID = 1 -- Admin THEN
(AND 1 = 1)
WHEN r1.RoleID = 2 -- Manager THEN
(
n.UserLogin IN (
SELECT DISTINCT ug.UserLogin
FROM security.UserGroups ug
WHERE ug.GroupID IN (
SELECTug1.GroupID
FROM security.UserGroups ug1
JOIN security.Groups g ON g.GroupID = ug1.GroupID
WHERE ug1.UserLogin = r1.UserLogin
AND g.GroupTypeID = 1
)
)
)
WHEN r1.RoleID = 3 -- Standard User THEN
(
n.UserLogin IN (
SELECT DISTINCT ug.UserLogin
FROM security.UserGroups ug
WHERE ug.GroupID IN (
SELECTug1.GroupID
FROM security.UserGroups ug1
JOIN security.Groups g ON g.GroupID = ug1.GroupID
WHERE ug1.UserLogin = r1.UserLogin
AND g.GroupTypeID = 1
)
AND n.Private = 0
OR (n.Private = 1 AND n.UserLogin = r1.UserLogin )
OR n.NoteID IN (
SELECT ID
FROM FirmMentions
WHERE TypeID = 1
AND UserLogin = r1.UserLogin
)
)
)
ELSE
(1 = 0)
END
FROM security.UserRoles r1
WHERE r1.UserLogin = 'asmith'
You can see the idea I'm trying to get to; getting to a where clause based on a person's role, then what groups they might be attached to. I feel like I'm close on this. Let me know.
Thank you.
October 4, 2016 at 3:49 pm
A CASE statement cannot return a Boolean value. You can however return a bit or string that represents a Boolean value and test whether it returns a token that represents true.
CASE
WHEN <condition 1> THEN 'T'
WHEN <condition 2> THEN 'F'
WHEN <condition 3> THEN 'T'
END = 'T'
That being said, CASE expressions in a WHERE clause are terrible, because they are not SARGable. You are better off looking for another approach, such as UNION to achieve your results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply