46
This code produces a table fine, however its when I try to pull data from it in Access is when I get the problem. The error I receive in access is: Insufficient number of arguments were supplied for the procedure or function.
ALTER FUNCTION dbo.R114ReportCopy (@Acyear smallint)
RETURNS @RtnTab table
(DeptName varchar(100),Acyear smallint,
Eid smallint,FacName varchar(50),
FormName varchar(100), FormNo smallint,
IndN smallint, DivN smallint, DepN smallint,
IndAvCont float, IndSDCont float,
IndAvVal float, IndSDVal float,
DivAvCont float, DivSDCont float,
DivAvVal float, DivSDVal float,
DepAvCont float, DepSDCont float,
DepAvVal float, DepSDVal float,
LIndAvCont float, LIndSDCont float,
LIndAvVal float, LIndSDVal float, LIndN smallint,
LDivAvCont float, LDivSDCont float,
LDivAvVal float, LDivSDVal float, LDivN smallint,
LDepAvCont float, LDepSDCont float,
LDepAvVal float, LDepSDVal float, LDepN smallint,
CIndAvCont float, CIndSDCont float,
CIndAvVal float, CIndSDVal float, CIndN smallint,
CDivAvCont float, CDivSDCont float,
CDivAvVal float, CDivSDVal float, CDivN smallint,
CDepAvCont float, CDepSDCont float,
CDepAvVal float, CDepSDVal float, CDepN smallint,
Comments text)
AS
BEGIN
Declare @LastYear smallint
Set @Lastyear=@Acyear-1
Insert into @RtnTab
Select dbo.deptcodename(dept) Department,Num.*, comments
from
(
Select @acyear acyear,T.*,
Lavcontact,Lsdcontact,Lavvalue,Lsdvalue,Ln,
Ldvcontactav,Ldvcontactsd,Ldvvalueav,Ldvvaluesd,Ld vn,
Ldepcontactav,Ldepcontactsd,Ldepvalueav,Ldepvalues d,Ldepn,
Cavcontact,Csdcontact,Cavvalue,Csdvalue,Cn,
Cdvcontactav,Cdvcontactsd,Cdvvalueav,Cdvvaluesd,Cd vn,
Cdepcontactav,Cdepcontactsd,Cdepvalueav,Cdepvalues d,Cdepn
from
(
Select eid,dbo.idtoname(eid) Name,description formname,f.formno,
N,DvN,DepN,
avcontact,sdcontact,AvValue,SdValue,
dvcontactAV,dvcontactSD,dvValueAv,dvValueSD,
depContactAV,depContactSD,depValueAv,DepValueSD from R114ByYear(@Acyear,@Acyear) R, R114FormName F
where R.formno=F.formno
) T left join
(
Select eid,formno,
avcontact Lavcontact,sdcontact Lsdcontact,avvalue Lavvalue,sdvalue Lsdvalue,n Ln,
dvcontactav Ldvcontactav,dvcontactsd Ldvcontactsd,dvvalueav Ldvvalueav,dvvaluesd Ldvvaluesd,dvn Ldvn,
depcontactav Ldepcontactav,depcontactsd Ldepcontactsd,depvalueav Ldepvalueav,depvaluesd Ldepvaluesd,depn Ldepn
from R114ByYear(@Lastyear,@Lastyear) L
) L on T.eid=L.eid and T.formno=L.formno
left join
(
Select eid,formno,
avcontact Cavcontact,sdcontact Csdcontact,avvalue Cavvalue,sdvalue Csdvalue,n Cn,
dvcontactav Cdvcontactav,dvcontactsd Cdvcontactsd,dvvalueav Cdvvalueav,dvvaluesd Cdvvaluesd,dvn Cdvn,
depcontactav Cdepcontactav,depcontactsd Cdepcontactsd,depvalueav Cdepvalueav,depvaluesd Cdepvaluesd,depn Cdepn
from R114ByYear(1995,@Acyear) C
) C on T.eid=C.eid and T.formno=c.formno
) Num Left join
(
Select eid,formtype formno, comments from R114
where acadyear=@Acyear
and (comments is not null
or cast(comments as varchar(10)) not in ('NULL','NA','none','n/a','n.a',' ') )
) comment on Num.eid=comment.eid and Num.formno=comment.formno
,RVUMasterList M
where
M.acyear=@acyear
and Num.eid=M.eid
order by 1,4,5
Return
END
