Catwoman Catwoman - 1 year ago 41
SQL Question

Alternate method to write the If method in the stored procedure

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


In the above stored procedure, when
@Getactiveonly
is true, then it should take only the users who are active else it should take both active and inactive users. i.e
IF (@GetactiveOnly = 1)
then it will fetch users whose
Isactive
is 1 only else it fetches all the users whose
IsActive
is 0 as well 1.

Could this be written in a simpler way to avoid duplication of select statements?

Answer Source

You can apply some more boolean logic, leading to this unified query:

SELECT ...
WHERE portalUser.Email = @Email
AND (@GetActiveOnly = 0 OR portalUser.IsActive = 1)

In words:

  • For 0, the first part is TRUE, making the second part irrelevant.
  • For 1, the first part is FALSE and then the second part determines the outcome.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download