Alternate method to write the If method in the SP

Question

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.LookUp`enter code here`PortalUserRoleId

                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 SP When

@Getactiveonly

is true, then it should take only the users who are active else it should take both active and inactive users. Ie

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.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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.

Alternate method to write the If method in the SP

Sign up