IS_MEMBER 的存储过程问题

发布于 2024-07-23 16:05:08 字数 426 浏览 4 评论 0原文

我有一个具有列 ID 的用户表，

ID nvarchar(4000)
GroupRank int
Definition nvarchar(4000)

可以是用户 ID（在这种情况下 groupRank 为 NULL）、具有排名的域组（在这种情况下 grouprank 不为空）或名为 #DefaultGroup 的保留默认组。

我需要一个存储过程，它将：

如果 ID=SYSTEM_USER，则返回该定义

否则 - foreach 记录在具有 GroupRank NOT NULL 的用户中，按组排名顺序，如果 IS_MEMBER(ID) =1，则返回该定义（如果有）

否则 - # DefaultGroup 定义（如果存在）

否则返回 NULL。

是否有捷径可寻？

原文

I have a table Users with columns

ID nvarchar(4000)
GroupRank int
Definition nvarchar(4000)

ID can be a userid (in which case groupRank is NULL), a domain group with a rank (in which case grouprank is not null) or a reserved default group called #DefaultGroup.

I need a stored procedure that will:

If ID=SYSTEM_USER, return that Definition

Otherwise - foreach record in Users with GroupRank NOT NULL in order of group rank, if IS_MEMBER(ID) =1, that definition (if any)

Otherwise - the #DefaultGroup definition (if it's there)

Otherwise return NULL.

Is there an easy way to do this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我纯我任性 2024-07-30 16:05:08

如果我理解正确，您也许可以使用类似于以下内容的某种 case 语句：

SELECT
  ID,
  GroupRank,
  Definition = CASE
                WHEN ID = SYSTEM_USER THEN Definition
                WHEN GroupRank IS NOT NULL AND IS_MEMBER(ID) = 1 THEN Definition
                WHEN ID = '#DefaultGroup' THEN Definition
                ELSE NULL
               END
FROM
     [YourTable]

If I'm understanding you correctly, you might be able to use some sort of case statement similar to the one below:

SELECT
  ID,
  GroupRank,
  Definition = CASE
                WHEN ID = SYSTEM_USER THEN Definition
                WHEN GroupRank IS NOT NULL AND IS_MEMBER(ID) = 1 THEN Definition
                WHEN ID = '#DefaultGroup' THEN Definition
                ELSE NULL
               END
FROM
     [YourTable]

回复收藏 0 原文

江南烟雨〆相思醉 2024-07-30 16:05:08

这似乎可以作为表函数使用

CREATE FUNCTION dbo.GetDefinition()
RETURNS @definition TABLE 
(
    Defn nvarchar(4000) NULL 
)
AS 
BEGIN
    DECLARE @sys_usr nvarchar(4000);
    DECLARE @def_usr nvarchar(4000);
    SET @sys_usr = SYSTEM_USER;
    SET @def_usr = '#Default';

    IF(EXISTS(SELECT * FROM dbo.User WHERE ID = @sys_usr))
    BEGIN
        INSERT @definition SELECT Definition FROM User WHERE ID = @sys_usr
    END ELSE
    BEGIN
        IF(EXISTS(SELECT TOP 1 * FROM dbo.User WHERE IS_MEMBER(ID) = 1 AND GroupRank IS NOT NULL ORDER BY GroupRank))
        BEGIN
            INSERT @definition SELECT TOP 1 Definition FROM dbo.User 
                WHERE IS_MEMBER(ID) = 1 AND GroupRank IS NOT NULL ORDER BY GroupRank
        END ELSE
        BEGIN
            IF(EXISTS(SELECT * FROM dbo.User WHERE ID = @def_usr))
            BEGIN
                INSERT @definition SELECT Definition FROM User WHERE ID = @def_usr
            END
        END
    END

    RETURN;
END

This seems to work as a table function

CREATE FUNCTION dbo.GetDefinition()
RETURNS @definition TABLE 
(
    Defn nvarchar(4000) NULL 
)
AS 
BEGIN
    DECLARE @sys_usr nvarchar(4000);
    DECLARE @def_usr nvarchar(4000);
    SET @sys_usr = SYSTEM_USER;
    SET @def_usr = '#Default';

    IF(EXISTS(SELECT * FROM dbo.User WHERE ID = @sys_usr))
    BEGIN
        INSERT @definition SELECT Definition FROM User WHERE ID = @sys_usr
    END ELSE
    BEGIN
        IF(EXISTS(SELECT TOP 1 * FROM dbo.User WHERE IS_MEMBER(ID) = 1 AND GroupRank IS NOT NULL ORDER BY GroupRank))
        BEGIN
            INSERT @definition SELECT TOP 1 Definition FROM dbo.User 
                WHERE IS_MEMBER(ID) = 1 AND GroupRank IS NOT NULL ORDER BY GroupRank
        END ELSE
        BEGIN
            IF(EXISTS(SELECT * FROM dbo.User WHERE ID = @def_usr))
            BEGIN
                INSERT @definition SELECT Definition FROM User WHERE ID = @def_usr
            END
        END
    END

    RETURN;
END

回复收藏 0 原文

~没有更多了~