Sql Server：where 条件中条件表达式的替代方案
在编写客户端服务器应用程序时，通常会遇到需要执行一项查询并根据登录用户返回不同记录的情况。
我使用这种方法，但我想知道是否有替代方案，您可以建议吗？
（我问的原因是，对于更复杂的查询，这变得非常不可读，而对于一些 CASE 语句，它会更好）。
declare @UserIsAdministrator bit;
declare @UserID integer;
set @UserIsAdministrator = 0 -- test value
set @UserID = 41; -- test value
SELECT * FROM employees
WHERE
(@UserIsAdministrator = 1) -- if user is admin return all records
OR
(
(@UserIsAdministrator = 0)
AND
(manager_ID = @UserID)
) -- if user is not admin return only "his" records
By writing client server applications typically one stumbles in cases where he needs to do a query that returns different records according to which is the logged in user.
I use this approach, but I wonder if there are alternatives, may you suggest?
(the reason I ask is that for more complx queries this becomes quite unreadable, while with some CASE statements it would be better).
declare @UserIsAdministrator bit;
declare @UserID integer;
set @UserIsAdministrator = 0 -- test value
set @UserID = 41; -- test value
SELECT * FROM employees
WHERE
(@UserIsAdministrator = 1) -- if user is admin return all records
OR
(
(@UserIsAdministrator = 0)
AND
(manager_ID = @UserID)
) -- if user is not admin return only "his" records
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我想我会尽量避免在 SQL 查询中完全嵌入这种类型的授权。在选择要执行的查询之前，拥有单独的查询，并在单独的层/组件中解析角色和权限。
I think I would try to avoid embedding this type of authorization within the SQL queries altogether. Have separate queries, and resolve roles and privileges in a separate layer/component before you select which query to execute.
选项：
IF 和 ELSE
根据另一个参数
或不同的视图/存储过程更改参数
Options:
IF and ELSE
Change parameters based on another parameter
Or different views/stored procs