SP ERROR: Procedure or function ORDER_ACTIVITY_REPORT has too many arg

[osjohnm]

Hi all

I have moved a report query into a stored proc. It brings back a list of orders which have purchase orders and orders without purchase orders between certain dates
there are 4 variables declared @startdate, @enddate, @name, @cost

so far the dates have to be passed but the @name and @cost is optional. these are the possible scenarios:

@name + @cost
@name but no @cost
no @name but @cost
no @name or @cost

the client needs to be able to choose only orders where the authoriser is someone or the cost centre is specified or both or neither

i first payed attention to where both aren't there or both are. i split my sp into two as such with the following

CREATE PROCEDURE [dbo].[ORDER_ACTIVITY_REPORT] @startdate as datetime, @enddate as datetime
AS
declare @name as varchar(50);
declare @cost as varchar(50)

if @name is null and @cost is null
select g.order_group_no, u.cost_centre, a.name[Authoriser], g.authorised_value[Auth Amount], sum(po.quantity * po.value)[PO Amount]
from order_group g, order_header h, purchase_header h, purchase_order po, user_info u, user_info a
where g.order_group_no = h.order_group_no
and h.order_no = ph.order_no
and ph.po_no = po.po_no
and ph.status_code <> 'cancelled'
and h.auth_date > @startdate
and h.auth_date < @enddate
and g.requestor = u.user_id
and g.authoriser = a.user_id
group by g.order_group_no, u.cost_centre, a.name, g.authorised_value
union
select g.order_group_no, u.cost_centre, a.name[Authoriser], g.authorised_value[Auth Amount], 0
from order_group g, order_header h, user_info u, user_info a
where g.order_group_no = h.order_group_no
and h.order_no not in (select order_no from purchase_header where order_no is not null)
and h.auth_date > @startdate
and h.auth_date < @enddate
and g.requestor = u.user_id
and g.authoriser = a.user_id
and g.order_group_no not in
(select g.order_group_no from order_group g, order_header h, purchase_header ph, purchase_order po, user_info u
where g.order_group_no = h.order_group_no
and h.order_no = ph.order_no
and ph.po_no = po.po_no
and ph.status_code <> 'cancelled'
and h.auth_date > @startdate
and h.auth_date < @enddate
and g.requestor = u.user_id)
group by g.order_group_no, u.cost_centre, a.name, g.authorised_value
order by g.order_group_no

if @name is not null and @cost is not null
select g.order_group_no, u.cost_centre, a.name[Authoriser], g.authorised_value[Auth Amount], sum(po.quantity * po.value)[PO Amount]
from order_group g, order_header h, purchase_header ph, purchase_order po, user_info u, user_info a
where g.order_group_no = h.order_group_no
and h.order_no = ph.order_no
and ph.po_no = po.po_no
and ph.status_code <> 'cancelled'
and h.auth_date > @startdate
and h.auth_date < @enddate
and g.requestor = u.user_id
and g.authoriser = a.user_id
and a.name like @name
and u.cost_centre like @cost
group by g.order_group_no, u.cost_centre, a.name, g.authorised_value
union
select g.order_group_no, u.cost_centre, a.name[Authoriser], g.authorised_value[Auth Amount], 0
from order_group g, order_header h, user_info u, user_info a
where g.order_group_no = h.order_group_no
and h.order_no not in (select order_no from purchase_header where order_no is not null)
and h.auth_date > @startdate
and h.auth_date < @enddate
and g.requestor = u.user_id
and g.authoriser = a.user_id
and a.name like @name
and u.cost_centre like @cost
and g.order_group_no not in
(select g.order_group_no from order_group g, order_header h, purchase_header ph, purchase_order po, user_info u
where g.order_group_no = h.order_group_no
and h.order_no = ph.order_no
and ph.po_no = po.po_no
and ph.status_code <> 'cancelled'
and h.auth_date > @startdate
and h.auth_date < @enddate
and g.requestor = u.user_id)
group by g.order_group_no, u.cost_centre, a.name, g.authorised_value
order by g.order_group_no
GO

here is what i'm passing for the null one
exec order_activity_report '2002-08-01', '2002-08-07'

and the not null check
exec order_activity_report '2002-08-01', '2002-08-07',
'%van dyk%', '%840%'

if i only run the not null i get a return same if i only use the null one but I can't get them to work together.
if i run it with both checks in i get this error:
Procedure or function ORDER_ACTIVITY_REPORT has too many arguments specified.

how do i specify all the scenarios in the stored proc?
and how do I get the correct records back?

thanks
john

 

[DangerPowers]

It looks as if you have two input parameters:

CREATE PROCEDURE [dbo].[ORDER_ACTIVITY_REPORT] @startdate as datetime, @enddate as datetime

But you are running it with 4 with the not null check:

exec order_activity_report '2002-08-01', '2002-08-07',
'%van dyk%', '%840%'

Try adding the @name and @cost variables to the input parameters as they are locally declared variables in the code you provided.