GetUserInfo根据用户ID或姓名取得该用户相关信息或部门信息的函数快表软件

楼主

本帖最后由 hessen 于 2021-8-24 16:24 编辑

GetUserInfo (参数1,参数2)

适用于：

快表软件企业版

返回指定用户姓名或用户ID的相关用户信息。

语法

GetUserInfo ( 用户姓名/用户ID,返回类型 )

参数

用户姓名/用户ID
快表系统中建立的用户姓名或用户编号

创建函数

CREATE FUNCTION [dbo].[GetUserInfo](@UserNameID VARCHAR(50),@Type VARCHAR(50))

returns VARCHAR(50)

as

Begin

declare  @return VARCHAR(50)



Begin

      IF @type = 'ID' --取得用户ID

         Begin

      SELECT @return = [UUID] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID

         END

      ELSE IF @type = '姓名' --取得用户姓名

         Begin

         SELECT @return = [USERNAME] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID

         END

      ELSE IF @type = '登录名' --取得用户登录名

         Begin

         SELECT @return = [LOGINNAME] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID

         END

      ELSE IF @type = '手机' --取得用户手机

         Begin

         SELECT @return = [USERMOBILE] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID

         END

      ELSE IF @type = '邮箱' --取得用户邮箱

         Begin

         SELECT @return = [USEREMAIL] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID

         END

      ELSE IF @type = '状态' --取得用户状态

         Begin

         SELECT @return = [ISENABLE] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID

         END

      ELSE IF @type = '备注' --取得用户备注

         Begin

         SELECT @return = [USERREMARK] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID

         END

               ELSE IF @type = '自定义字符1' --取得用户自定义字符1

         Begin

         SELECT @return = [CUSTOMTEXT1] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID

         END

               ELSE IF @type = '自定义字符2' --取得用户自定义字符2

         Begin

         SELECT @return = [CUSTOMTEXT2] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID

         END

               ELSE IF @type = '自定义字符3' --取得用户自定义字符3

         Begin

         SELECT @return = [CUSTOMTEXT3] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID

         END

               ELSE IF @type = '自定义字符4' --取得用户自定义字符4

         Begin

         SELECT @return = [CUSTOMTEXT4] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID

         END

               ELSE IF @type = '自定义字符5' --取得用户自定义字符5

         Begin

         SELECT @return = [CUSTOMTEXT5] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID

         END

      ELSE IF @type = '部门ID' --取得用户所在部门ID

         Begin

         SELECT @return = [DID] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID

         END

   ELSE IF @type = '部门' --取得用户所在部门名称

         Begin

         SELECT @return = [DEPTNAME] FROM SYS_DEPT WHERE DID = (SELECT [DID] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID)

         END

      ELSE IF @type = '部门编号' --取得用户所在部门编号

         Begin

               SELECT @return = [DEPTNUMBER] FROM SYS_DEPT WHERE DID = (SELECT [DID] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID)

         END

      ELSE IF @type = '上级部门ID' --取得用户上级部门ID

         Begin

               SELECT @return = [PID] FROM SYS_DEPT WHERE DID = (SELECT [DID] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID)

         END

      ELSE IF @type = '上级部门名称' --取得用户上级部门名称

         Begin

               SELECT @return = [DEPTNAME] FROM SYS_DEPT WHERE DID = (SELECT PID FROM SYS_DEPT WHERE DID = (SELECT [DID] FROM [SYS_USER] WHERE USERNAME = @UserNameID OR UUID =  @UserNameID))

         END

End

return(@return)

End

GO
复制代码

示例
A. 使用 GetUserInfo 取得用户手机

SELECT [dbo].[GetUserInfo]('李荣坤','手机')

复制代码

hessen

产品与服务

网站地图