USP_PROSPECTSEGMENTATION_SEGMENT_PROPERTIES

Returns properties of a Prospect Segmentation segment given the primary and secondary section IDs.
Parameters

Parameter	Parameter Type	Mode
@PRIMARYSECTIONID	uniqueidentifier	IN
@SECONDARYSECTIONID	uniqueidentifier	IN
@PRIMARYVARIABLETYPE	nvarchar(50)	INOUT
@SECONDARYVARIABLETYPE	nvarchar(50)	INOUT
@SECONDARYVARIABLE2TYPE	nvarchar(50)	INOUT
@PRIMARYVARIABLETYPECODE	smallint	INOUT
@SECONDARYVARIABLETYPECODE	smallint	INOUT
@SECONDARYVARIABLE2TYPECODE	smallint	INOUT
@PRIMARYRANGELOW	int	INOUT
@PRIMARYRANGEHIGH	int	INOUT
@PRIMARYAMOUNTLOWID	uniqueidentifier	INOUT
@PRIMARYAMOUNTHIGHID	uniqueidentifier	INOUT
@SECONDARYRANGELOW	int	INOUT
@SECONDARYRANGEHIGH	int	INOUT
@SECONDARYAMOUNTLOWID	uniqueidentifier	INOUT
@SECONDARYAMOUNTHIGHID	uniqueidentifier	INOUT
@SECONDARYRANGE2LOW	int	INOUT
@SECONDARYRANGE2HIGH	int	INOUT
@SECONDARYAMOUNT2LOWID	uniqueidentifier	INOUT
@SECONDARYAMOUNT2HIGHID	uniqueidentifier	INOUT
Definition

Copy


            create procedure dbo.USP_PROSPECTSEGMENTATION_SEGMENT_PROPERTIES (
                    @PRIMARYSECTIONID uniqueidentifier,
                    @SECONDARYSECTIONID uniqueidentifier,      
                    @PRIMARYVARIABLETYPE nvarchar(50)= null output,
          @SECONDARYVARIABLETYPE nvarchar(50) = null output,
          @SECONDARYVARIABLE2TYPE nvarchar(50) = null output,

                    @PRIMARYVARIABLETYPECODE smallint = null output,
                    @SECONDARYVARIABLETYPECODE smallint = null output,
                    @SECONDARYVARIABLE2TYPECODE smallint = null output,

          @PRIMARYRANGELOW int = null output,
          @PRIMARYRANGEHIGH int = null output,
          @PRIMARYAMOUNTLOWID uniqueidentifier = null output,
          @PRIMARYAMOUNTHIGHID uniqueidentifier = null output,

          @SECONDARYRANGELOW int = null output,
          @SECONDARYRANGEHIGH int = null output,
          @SECONDARYAMOUNTLOWID uniqueidentifier = null output,
          @SECONDARYAMOUNTHIGHID uniqueidentifier = null output,

          @SECONDARYRANGE2LOW int = null output,
          @SECONDARYRANGE2HIGH int = null output,
          @SECONDARYAMOUNT2LOWID uniqueidentifier = null output,
          @SECONDARYAMOUNT2HIGHID uniqueidentifier = null output

                ) as
                    set nocount on;

                    select
                        @PRIMARYVARIABLETYPE =      PRIMARYVARIABLE.TYPE,
                        @PRIMARYVARIABLETYPECODE =  PRIMARYVARIABLE.TYPECODE,
            @PRIMARYRANGELOW  =         PRIMARYSECTION.RANGELOW,
            @PRIMARYRANGEHIGH =         PRIMARYSECTION.RANGEHIGH,

            @PRIMARYAMOUNTLOWID =       case PRIMARYVARIABLE.TYPECODE
                                            when 10 then PRIMARYSECTION.ESTIMATEDWEALTHLOWID
                                            when 11 then PRIMARYSECTION.MAJORGIVINGCAPACITYLOWID
                                            when 12 then PRIMARYSECTION.TARGETGIFTRANGELOWID
                                            when 13 then PRIMARYSECTION.WEALTHESTIMATORRATINGLOWID
                                            else null
                                        end,
            @PRIMARYAMOUNTHIGHID =      case PRIMARYVARIABLE.TYPECODE
                                            when 10 then PRIMARYSECTION.ESTIMATEDWEALTHHIGHID
                                            when 11 then PRIMARYSECTION.MAJORGIVINGCAPACITYHIGHID
                                            when 12 then PRIMARYSECTION.TARGETGIFTRANGEHIGHID
                                            when 13 then PRIMARYSECTION.WEALTHESTIMATORRATINGHIGHID
                                            else null
                                        end

                    from
                        dbo.PROSPECTSEGMENTATIONSECTION PRIMARYSECTION
                    left join dbo.PROSPECTSEGMENTATIONVARIABLE PRIMARYVARIABLE on
                        PRIMARYVARIABLE.ID = PRIMARYSECTION.VARIABLEID
                    where
                        PRIMARYSECTION.ID = @PRIMARYSECTIONID

                    select
                    @SECONDARYVARIABLETYPE =      SECONDARYVARIABLE.TYPE,
                        @SECONDARYVARIABLETYPECODE =  SECONDARYVARIABLE.TYPECODE,
            @SECONDARYVARIABLE2TYPE =     case SECONDARYSECTION.LABEL2
                                            when '' then ''
                                            else  SECONDARYVARIABLE.TYPE2
                                          end,
            @SECONDARYVARIABLE2TYPECODE = case SECONDARYSECTION.LABEL2
                                            when '' then 0
                                            else  SECONDARYVARIABLE.TYPE2CODE
                                          end,

            @SECONDARYRANGELOW  =         SECONDARYSECTION.RANGELOW,
            @SECONDARYRANGEHIGH =         SECONDARYSECTION.RANGEHIGH,
            @SECONDARYRANGE2LOW  =        SECONDARYSECTION.RANGE2LOW,
            @SECONDARYRANGE2HIGH =        SECONDARYSECTION.RANGE2HIGH,                          
            @SECONDARYAMOUNTLOWID =       case SECONDARYVARIABLE.TYPECODE
                                            when 10 then SECONDARYSECTION.ESTIMATEDWEALTHLOWID
                                            when 11 then SECONDARYSECTION.MAJORGIVINGCAPACITYLOWID
                                            when 12 then SECONDARYSECTION.TARGETGIFTRANGELOWID
                                            when 13 then SECONDARYSECTION.WEALTHESTIMATORRATINGLOWID
                                            else null
                                         end,
            @SECONDARYAMOUNTHIGHID =     case SECONDARYVARIABLE.TYPECODE
                                            when 10 then SECONDARYSECTION.ESTIMATEDWEALTHHIGHID
                                            when 11 then SECONDARYSECTION.MAJORGIVINGCAPACITYHIGHID
                                            when 12 then SECONDARYSECTION.TARGETGIFTRANGEHIGHID
                                            when 13 then SECONDARYSECTION.WEALTHESTIMATORRATINGHIGHID
                                            else null
                                         end,

            @SECONDARYAMOUNT2LOWID =     case SECONDARYVARIABLE.TYPE2CODE
                                            when 10 then SECONDARYSECTION.ESTIMATEDWEALTH2LOWID
                                            when 11 then SECONDARYSECTION.MAJORGIVINGCAPACITY2LOWID
                                            when 12 then SECONDARYSECTION.TARGETGIFTRANGE2LOWID
                                            when 13 then SECONDARYSECTION.WEALTHESTIMATORRATING2LOWID
                                            else null
                                         end,
            @SECONDARYAMOUNT2HIGHID =    case SECONDARYVARIABLE.TYPE2CODE
                                            when 10 then SECONDARYSECTION.ESTIMATEDWEALTH2HIGHID
                                            when 11 then SECONDARYSECTION.MAJORGIVINGCAPACITY2HIGHID
                                            when 12 then SECONDARYSECTION.TARGETGIFTRANGE2HIGHID
                                            when 13 then SECONDARYSECTION.WEALTHESTIMATORRATING2HIGHID
                                            else null
                                         end
                    from
                        dbo.PROSPECTSEGMENTATIONSECTION SECONDARYSECTION
                    left join dbo.PROSPECTSEGMENTATIONVARIABLE SECONDARYVARIABLE on
                        SECONDARYVARIABLE.ID = SECONDARYSECTION.VARIABLEID
                    where
                        SECONDARYSECTION.ID = @SECONDARYSECTIONID