USP_SPONSORSHIP_MATCHOPPORTUNITY

Assigns an opportunity for a sponsorship based on preferences and greatest need.

Parameters

Parameter	Parameter Type	Mode
@MATCHEDOPPORTUNITYID	uniqueidentifier	INOUT
@SPONSORSHIPPROGRAMID	uniqueidentifier	IN
@MATCHRULE	tinyint	IN
@SPONSORSHIPLOCATIONID	uniqueidentifier	IN
@GENDERCODE	tinyint	IN
@SPROPPAGERANGEID	uniqueidentifier	IN
@ISHIVPOSITIVECODE	tinyint	IN
@HASCONDITIONCODE	tinyint	IN
@ISORPHANEDCODE	tinyint	IN
@SPROPPPROJECTCATEGORYCODEID	uniqueidentifier	IN
@SPONSORID	uniqueidentifier	IN
@ISSOLESPONSORSHIP	bit	IN
@EXCLUDEOPPORTUNITYID	uniqueidentifier	IN
@ORIGINALLOCATIONID	uniqueidentifier	IN
@REVENUECONSTITUENTID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_SPONSORSHIP_MATCHOPPORTUNITY (
    @MATCHEDOPPORTUNITYID uniqueidentifier output,
    @SPONSORSHIPPROGRAMID uniqueidentifier,
    @MATCHRULE tinyint = 0,        -- 0=Greatest need, 1=Nearest location

    @SPONSORSHIPLOCATIONID uniqueidentifier = null,
    @GENDERCODE tinyint = 0,
    @SPROPPAGERANGEID uniqueidentifier = null,
    @ISHIVPOSITIVECODE tinyint = 0,
    @HASCONDITIONCODE tinyint = 0,
    @ISORPHANEDCODE tinyint = 0,
    @SPROPPPROJECTCATEGORYCODEID uniqueidentifier = null,
    @SPONSORID uniqueidentifier = null,
    @ISSOLESPONSORSHIP bit = 0,
    @EXCLUDEOPPORTUNITYID uniqueidentifier = null,
    @ORIGINALLOCATIONID uniqueidentifier = null,
    @REVENUECONSTITUENTID uniqueidentifier = null
)
with execute as owner
as
begin
    set nocount on;

    declare @SQLSTRING nvarchar(400)
    declare @PARAMS nvarchar(500)

    select @SQLSTRING = N'select @SPONSORSHIPOPPORTUNITYID = dbo.' +
                            SQLFUNCTIONCATALOG.FUNCTIONNAME +
                             N'(@SPONSORSHIPPROGRAMID,' +
                              N'@MATCHRULE,' +
                              N'@SPONSORSHIPLOCATIONID,' +
                              N'@GENDERCODE,' +
                              N'@SPROPPAGERANGEID,' +
                              N'@ISHIVPOSITIVECODE,' +
                              N'@HASCONDITIONCODE,' +
                              N'@ISORPHANEDCODE,' +
                              N'@SPROPPPROJECTCATEGORYCODEID,' +
                              N'@SPONSORID,' +
                              N'@ISSOLESPONSORSHIP,' +
                              N'@EXCLUDEOPPORTUNITYID,' +
                              N'@ORIGINALLOCATIONID,' +
                              N'@REVENUECONSTITUENTID)'
    from dbo.SPONSORSHIPPROGRAM
    inner join dbo.SQLFUNCTIONCATALOG on SQLFUNCTIONCATALOG.ID = SPONSORSHIPPROGRAM.MATCHFUNCTIONID
    where SPONSORSHIPPROGRAM.ID = @SPONSORSHIPPROGRAMID

    set @PARAMS = N'@SPONSORSHIPOPPORTUNITYID uniqueidentifier output,' +
                  N'@SPONSORSHIPPROGRAMID uniqueidentifier,' +
                  N'@MATCHRULE tinyint,' +
                  N'@SPONSORSHIPLOCATIONID uniqueidentifier,' +
                  N'@GENDERCODE tinyint,' +
                  N'@SPROPPAGERANGEID uniqueidentifier,' +
                  N'@ISHIVPOSITIVECODE tinyint,' +
                  N'@HASCONDITIONCODE tinyint,' +
                  N'@ISORPHANEDCODE tinyint,' +
                  N'@SPROPPPROJECTCATEGORYCODEID uniqueidentifier,' +
                  N'@SPONSORID uniqueidentifier,' +
                  N'@ISSOLESPONSORSHIP bit,' +
                  N'@EXCLUDEOPPORTUNITYID uniqueidentifier,' +
                  N'@ORIGINALLOCATIONID uniqueidentifier,' +
                  N'@REVENUECONSTITUENTID uniqueidentifier';

    exec sp_executesql @SQLSTRING,
                       @PARAMS,
                       @MATCHEDOPPORTUNITYID output,
                       @SPONSORSHIPPROGRAMID,
                       @MATCHRULE,
                       @SPONSORSHIPLOCATIONID,
                       @GENDERCODE,
                       @SPROPPAGERANGEID,
                       @ISHIVPOSITIVECODE,
                       @HASCONDITIONCODE,
                       @ISORPHANEDCODE,
                       @SPROPPPROJECTCATEGORYCODEID,
                       @SPONSORID,
                       @ISSOLESPONSORSHIP,
                       @EXCLUDEOPPORTUNITYID,
                       @ORIGINALLOCATIONID,
                       @REVENUECONSTITUENTID;

    return 0;
end