USP_GROUPMEMBERROLE_VALIDDATES

This stored procedure is used to validate dates on a group member role are valid.
Parameters

Parameter	Parameter Type	Mode
@GROUPMEMBERID	uniqueidentifier	IN
@STARTDATE	date	IN
@ENDDATE	date	IN
Definition

Copy


            CREATE procedure dbo.USP_GROUPMEMBERROLE_VALIDDATES
            (
                @GROUPMEMBERID uniqueidentifier,
                @STARTDATE date,
                @ENDDATE date
            )
            as
                set nocount on;

                declare @CURRENTDATE date;
                set @CURRENTDATE = getdate();

                declare @GROUPMEMBERSTARTDATE date;
                declare @GROUPMEMBERENDDATE date;

                select
                    @GROUPMEMBERSTARTDATE = DATEFROM,
                    @GROUPMEMBERENDDATE = DATETO
                from
                    dbo.GROUPMEMBERDATERANGE
                where GROUPMEMBERID = @GROUPMEMBERID

                if @STARTDATE is not null
                begin
                    if @STARTDATE > @CURRENTDATE
                    begin    
                        raiserror('ERR_STARTDATE_CANNOT_BE_IN_FUTURE', 13, 1)
                        return 1
                    end

                    if @STARTDATE < @GROUPMEMBERSTARTDATE
                    begin
                        raiserror('ERR_STARTDATE_CANNOT_BE_BEFORE_MEMBERSTART', 13, 1)
                        return 1
                    end

                    if @STARTDATE > @GROUPMEMBERENDDATE
                    begin
                        raiserror('ERR_STARTDATE_CANNOT_BE_AFTER_MEMBEREND', 13, 1)
                        return 1
                    end
                end

                if @ENDDATE is not null
                begin
                    if @ENDDATE < @GROUPMEMBERSTARTDATE
                    begin
                        raiserror('ERR_ENDDATE_CANNOT_BE_BEFORE_MEMBERSTART', 13, 1)
                        return 1
                    end                

                    if @ENDDATE > @GROUPMEMBERENDDATE
                    begin
                        raiserror('ERR_ENDDATE_CANNOT_BE_AFTER_MEMBEREND', 13, 1)
                        return 1
                    end
                end

                return 0;