UFN_BANKACCOUNT_ROUTINGUNIQUE

This checks to make sure the routing number does not already exist on a bank or bank account.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode
@BANKID	uniqueidentifier	IN
@BANKACCOUNTID	uniqueidentifier	IN
@ROUTINGNUMBER	nvarchar(9)	IN

Definition

Copy



            create function dbo.UFN_BANKACCOUNT_ROUTINGUNIQUE
            (
                @BANKID uniqueidentifier,
                @BANKACCOUNTID uniqueidentifier,
                @ROUTINGNUMBER nvarchar(9)
            )

            returns bit
            with execute as caller
            as begin

            if exists(
                select
                    ROUTINGNUMBER
                from
                    dbo.BANKACCOUNT
                where
                    BANKACCOUNT.BANKID <> @BANKID AND
                    BANKACCOUNT.ID <> @BANKACCOUNTID AND
                    BANKACCOUNT.ROUTINGNUMBER = @ROUTINGNUMBER
                    AND @ROUTINGNUMBER <> ''
            ) 

            or
             exists(
                select
                    DEFAULTROUTINGNUMBER
                from
                    dbo.BANK
                where
                    BANK.ID <> @BANKID AND
                    BANK.DEFAULTROUTINGNUMBER = @ROUTINGNUMBER 
                    AND @ROUTINGNUMBER <> ''
            )

            return 1;


            return 0;
            end