ALTER PROCEDURE [User].GetUserByEmail
@Email VARCHAR(200),
@GetActiveOnly BIT
AS
BEGIN
IF (@GetActiveOnly = 1)
BEGIN
SELECT
portalUser.PortalUserId AS 'UserId',
organisation.OrganisationId AS 'OrganisationId',
organisation.LookUpOrganisationTypeId AS 'OrganisationTypeId'
FROM
Admin.PortalUser portalUser
INNER JOIN
Admin.LookUpPortalUserRole userRole ON userRole.LookUpPortalUserRoleId = portalUser.LookUpPortalUserRoleId
INNER JOIN
Admin.Organisation organisation ON organisation.OrganisationId = portalUser.OrganisationId
WHERE
portalUser.Email = @Email
AND portalUser.IsActive = 1
END
ELSE
BEGIN
SELECT
portalUser.PortalUserId AS 'UserId',
organisation.OrganisationId AS 'OrganisationId',
organisation.LookUpOrganisationTypeId AS 'OrganisationTypeId'
FROM
Admin.PortalUser portalUser
INNER JOIN
Admin.LookUpPortalUserRole userRole ON userRole.LookUpPortalUserRoleId = portalUser.LookUpPortalUserRoleId
INNER JOIN
Admin.Organisation organisation ON organisation.OrganisationId = portalUser.OrganisationId
WHERE
portalUser.Email = @Email
END
END
在上面的存储过程中,当@Getactiveonly
为 true 时,它应该只接受活动用户,否则它应该同时接受活动和非活动用户。 即IF (@GetactiveOnly = 1)
,它将获取Isactive
为 1 的用户,否则它将获取IsActive
为 0 的所有用户以及 1。
是否可以以更简单的方式编写以避免重复选择语句?
您可以应用更多的布尔逻辑,从而导致此统一查询:
SELECT ...
WHERE portalUser.Email = @Email
AND (@GetActiveOnly = 0 OR portalUser.IsActive = 1)
用词来说:
- 对于 0,第一部分为 TRUE,使第二部分无关紧要。
- 对于 1,第一部分为 FALSE,然后第二部分确定结果。