• Replies 4 replies
  • Subscribers 338 subscribers
  • Views 30 views
  • Users 0 members are here
Related

LP_GET_CONTR! too many arguments

Richard Jackson (Past Member)
$organization

Howdy,

I was told this was the place to go to for custom programming troubleshooting.  We wrote the following procedure to customize gettig suggested contribution amounts for our subscrption rollover.  However, it keeps coming back with "too many arguments".  Is there any chance someone could let me know where this is coming from?  Any assistance is appreciated!

Richard Jackson, Alley Theatre

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

USE

 

 

[impresario]

GO

/****** Object: StoredProcedure [dbo].[LP_GET_CONTR1] Script Date: 02/07/2011 09:24:18 ******/

SET

 

 

ANSI_NULLS

OFF

GO

SET

 

 

QUOTED_IDENTIFIER

OFF

GO

 

ALTER

 

 

PROCEDURE [dbo].

[LP_GET_CONTR1]

(

-- @LIST_NO INT = NULL,

@CUSTOMER_NO

 

INT

)

AS

Set

 

 

NoCount On

-- added auto CWR 8/9/2001

DECLARE

 

 

@CYGIVING MONEY =

0

DECLARE

 

 

@CYCREDITS MONEY =

0

DECLARE

 

 

@LYGIVING MONEY =

0

DECLARE

 

 

@LYCREDITS MONEY =

0

--SET @LIST_NO = 12

-- SET @CUSTOMER_NO = 586 -- TESTING ON M. LONG --

--SET @CUSTOMER_NO = (SELECT CUSTOMER_NO FROM T_LIST_CONTENTS WHERE LIST_NO = @LIST_NO)

/***** GET CURRENT YEAR CONTRIBUTIONS *****/

-- GET THE CURRENT FISCAL YEAR --

declare

 

 

@cyfyear

int

if

 

 

(month(getdate())>=7

)

 

 

begin

 

 

set @cyfyear=year(getdate()+1

)

 

 

end

else

 

 

begin

 

 

set @cyfyear=year(getdate

())

 

 

end

--select @cyfyear'CURRENT_FISCAL_YEAR'

-- GOT IT --

-- GET INDIVIDUAL GIVING --

SELECT

 

 

@CYGIVING = SUM(B.CONT_AMT

)

FROM

 

 

T_CUSTOMER A

 

 

LEFT JOIN T_CONTRIBUTION B ON A.CUSTOMER_NO=B.

CUSTOMER_NO

 

 

LEFT JOIN T_CAMPAIGN D ON B.CAMPAIGN_NO=D.

CAMPAIGN_NO

WHERE

 

 

A.CUSTOMER_NO =

@CUSTOMER_NO

 

 

AND D.DESCRIPTION LIKE

'%Annual Individual Giving'

 

 

AND D.FYEAR =

@CYFYEAR

 

 

AND B.REF_NO NOT IN (SELECT REF_NO FROM T_CREDITEE

)

GROUP

 

 

BY A.

CUSTOMER_NO

 

-- GET CREDITED GIVING --

SELECT

 

 

@CYCREDITS = SUM(B.CREDIT_AMT

)

FROM

 

 

T_CUSTOMER A

 

 

LEFT JOIN T_CREDITEE B ON A.CUSTOMER_NO = B.

CREDITEE_NO

 

 

LEFT JOIN T_CONTRIBUTION C ON B.REF_NO=C.

REF_NO

 

 

LEFT JOIN T_CAMPAIGN D ON C.CAMPAIGN_NO=D.

CAMPAIGN_NO

WHERE

 

 

A.CUSTOMER_NO =

@CUSTOMER_NO

 

 

AND D.DESCRIPTION LIKE

'%Annual Individual Giving'

 

 

AND D.FYEAR =

@CYFYEAR

GROUP

 

 

BY A.

CUSTOMER_NO

-- GET LY INDIVIDUAL GIVING --

SELECT

 

 

@LYGIVING = SUM(B.CONT_AMT

)

FROM

 

 

T_CUSTOMER A

 

 

LEFT JOIN T_CONTRIBUTION B ON A.CUSTOMER_NO=B.

CUSTOMER_NO

 

 

LEFT JOIN T_CAMPAIGN D ON B.CAMPAIGN_NO=D.

CAMPAIGN_NO

WHERE

 

 

A.CUSTOMER_NO =

@CUSTOMER_NO

 

 

AND D.DESCRIPTION LIKE

'2010 Annual Individual Giving'

-- AND D.FYEAR = @CYFYEAR-1

 

 

AND B.REF_NO NOT IN (SELECT REF_NO FROM T_CREDITEE

)

GROUP

 

 

BY A.

CUSTOMER_NO

-- GET LY CREDITED GIVING --

SELECT

 

 

@LYCREDITS = SUM(B.CREDIT_AMT

)

FROM

 

 

T_CUSTOMER A

 

 

LEFT JOIN T_CREDITEE B ON A.CUSTOMER_NO = B.

CREDITEE_NO

 

 

LEFT JOIN T_CONTRIBUTION C ON B.REF_NO=C.

REF_NO

 

 

LEFT JOIN T_CAMPAIGN D ON C.CAMPAIGN_NO=D.

CAMPAIGN_NO

WHERE

 

 

A.CUSTOMER_NO =

@CUSTOMER_NO

 

 

AND D.DESCRIPTION =

'2010 Annual Individual Giving'

-- AND D.FYEAR = @CYFYEAR-1

GROUP

 

 

BY A.

CUSTOMER_NO

DECLARE

 

 

@CYTOTAL

MONEY

SET

 

 

@CYTOTAL = @CYGIVING+

@CYCREDITS

DECLARE

 

 

@LYTOTAL

MONEY

SET

 

 

@LYTOTAL = @LYGIVING+

@LYCREDITS

DECLARE

 

 

@SUGGESTED_CONTR_AMT_1

MONEY

SELECT

 

 

@SUGGESTED_CONTR_AMT_1 = CASE WHEN @CYTOTAL BETWEEN .01 AND 299.99 THEN

300

 

 

WHEN @CYTOTAL BETWEEN 300 AND 4999.99 THEN

@CYTOTAL

 

 

WHEN @CYTOTAL >=5000 THEN

0

 

 

WHEN @CYTOTAL = 0 AND @LYTOTAL BETWEEN .01 AND 299.99 THEN

300

 

 

WHEN @CYTOTAL = 0 AND @LYTOTAL BETWEEN 300 AND 4999.99 THEN

@LYTOTAL

 

 

WHEN @CYTOTAL = 0 AND @LYTOTAL >= 5000 THEN

0

 

 

ELSE 300

END

 

SELECT

 

 

@SUGGESTED_CONTR_AMT_1

'@suggested_contr_amt_1'

Parents
  • $organization

    The parameter set should be the same as the original sample procedure.  These parameters are expected (even if not used) by the application.

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Richard Jackson
    Sent: Monday, February 07, 2011 3:24 PM
    To: Ryan Creps
    Subject: Re: [Tessitura Technical Forum] LP_GET_CONTR! too many arguments

     

    Hi Brian - thanks for the response!

    @list_no was left over from me testing the SP against a list of folks - I commented it out so that i wouldn't be required. 

    The procedure is being called when a subscription rollover is done to populate a suggested contribution field in T_CUST_SUBSCRIPTION_SUMMARY.  My understanding was all I would need from the SP was to return a suggested contribution amount.  Since our logic is farily simple, I eliminated the other data Tessitura's sample procedure called for (source_no, no_seats, season_no, etc..) and set the SP to only require a customer_no to run.

    Does that help?

    From: Brian Graham <bounce-briangraham8283@tessituranetwork.com>
    Sent: 2/7/2011 2:14:02 PM

    Looks like @list_no has been commented out in your sp. Are you still trying to pass that when you run it? Also, are you running this through an infomaker report or directly in Management Studio?




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Reply
  • $organization

    The parameter set should be the same as the original sample procedure.  These parameters are expected (even if not used) by the application.

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Richard Jackson
    Sent: Monday, February 07, 2011 3:24 PM
    To: Ryan Creps
    Subject: Re: [Tessitura Technical Forum] LP_GET_CONTR! too many arguments

     

    Hi Brian - thanks for the response!

    @list_no was left over from me testing the SP against a list of folks - I commented it out so that i wouldn't be required. 

    The procedure is being called when a subscription rollover is done to populate a suggested contribution field in T_CUST_SUBSCRIPTION_SUMMARY.  My understanding was all I would need from the SP was to return a suggested contribution amount.  Since our logic is farily simple, I eliminated the other data Tessitura's sample procedure called for (source_no, no_seats, season_no, etc..) and set the SP to only require a customer_no to run.

    Does that help?

    From: Brian Graham <bounce-briangraham8283@tessituranetwork.com>
    Sent: 2/7/2011 2:14:02 PM

    Looks like @list_no has been commented out in your sp. Are you still trying to pass that when you run it? Also, are you running this through an infomaker report or directly in Management Studio?




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Children
No Data