UFN_ORGANIZATIONPOSITIONHOLDER_POSITIONISUNIQUE

Validates whether the position is unique.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@POSITIONID	uniqueidentifier	IN
@DATEFROM	datetime	IN
@DATETO	datetime	IN

Definition

Copy



        CREATE function dbo.UFN_ORGANIZATIONPOSITIONHOLDER_POSITIONISUNIQUE
        (
            @POSITIONID uniqueidentifier,
            @DATEFROM datetime,
            @DATETO datetime
        )
        returns bit
        with execute as caller
        as begin
            declare @DUPLICATECOUNT int;

            select
                @DUPLICATECOUNT = count(*)
            from
                dbo.ORGANIZATIONPOSITIONHOLDER
            where
                POSITIONID = @POSITIONID and
                (
                    ( DATETO   between @DATEFROM and @DATETO) or
                    (@DATETO   between  DATEFROM and  DATETO) or
                    ( DATEFROM between @DATEFROM and @DATETO) or
                    (@DATEFROM between  DATEFROM and  DATETO) or

                    ( DATEFROM is null and @DATEFROM <=  DATETO)   or
                    (@DATEFROM is null and  DATEFROM <= @DATETO)   or
                    ( DATETO   is null and @DATETO   >=  DATEFROM) or
                    (@DATETO   is null and  DATETO   >= @DATEFROM) or

                    (@DATEFROM is null and  DATEFROM is null) or
                    (@DATETO   is null and  DATETO   is null) or
                    (@DATEFROM is null and @DATETO   is null) or
                    ( DATEFROM is null and  DATETO   is null)
                );

            if (@DUPLICATECOUNT <= 1)
                return 1;

            return 0;
        end