UFN_MEMBERSHIPCARD_VALIDNUMBEROFCARDS

        CREATE function dbo.UFN_MEMBERSHIPCARD_VALIDNUMBEROFCARDS
        (
            @MEMBERID uniqueidentifier
        )
        returns bit
        with execute as caller
        as begin

            declare @MEMBERSHIPID uniqueidentifier
            declare @CARDSALLOWED smallint

            select @MEMBERSHIPID = M.MEMBERSHIPID,
                @CARDSALLOWED = ML.CARDSALLOWED
            from dbo.MEMBER M
            inner join dbo.MEMBERSHIP MB
                on M.MEMBERSHIPID = MB.ID
            inner join dbo.MEMBERSHIPLEVEL ML
                on MB.MEMBERSHIPLEVELID = ML.ID
            where M.ID = @MEMBERID

            declare @CARDCOUNT smallint

            select @CARDCOUNT = count(MC.ID)
            -- JLM 3/6/2012 : WI 199320

            /*    To relieve resource contention, use 'nolock' hint here since
                the query is using the context of the given membership. */
            from dbo.MEMBERSHIPCARD MC with (nolock)
            inner join dbo.MEMBER M
            on M.ID = MC.MEMBERID
            where M.MEMBERSHIPID = @MEMBERSHIPID
                and MC.STATUSCODE <> 2

            if @CARDCOUNT > @CARDSALLOWED --and @CARDSALLOWED > 0

                return 0;

            return 1;
        end