求助:怎样写存储过程
这是ACCESS中报表执行时赋值给报表的记录源:
Me.RecordSource = "SELECT A.Fpass,A.FfatherOddnumber,C.IDNAME AS WEREHOUSE," & _
"D.IDNAME AS BILL,A.Fdate,A.Fremark," & _
"A.FwrikeMr,B.IDNo,F.FCname," & _
"F.Fdrawing,F.Fsizespec,G.IDNAME AS UNIT," & _
"B.FmaPrice,B.FintoQuty,B.FSum," & _
"B.Fremark AS REMARK,E.idname AS DEPSC," & _
"A.FdelGoNo,b.Fnumber " & _
"FROM ((((tabAccountlistA AS A " & _
"LEFT JOIN tabAccountlistB AS B ON A.FfatherOddnumber=B.FfatherOddnumber) " & _
"LEFT JOIN tabWarehouseIDS AS C ON A.Fwerehousenumber=C.IDno) " & _
"LEFT JOIN tabBillCalssS AS D ON A.BILLCALSS=D.IDno) " & _
"LEFT JOIN SqlDepSupCust AS E ON A.Fdeptnumber=E.idno) " & _
"LEFT JOIN (tabT_item AS F " & _
"LEFT JOIN tabUnitIDS G ON F.Funit=G.IDno) ON B.Fnumber=F.Fnumber " & _
"WHERE (((A.Fpass)=True) And ((A.FfatherOddnumber)='" & Forms.frmWXgoHome.TxtIntoW & "'))"
Forms.frmWXgoHome.TxtIntoW是窗体上一个文本框内容要传递给语句,
求助,在SQL中怎样把这写成存储过程,再报表执行时调用这个存储过程赋值给报表记录源
在此先谢谢各位了
[解决办法]
create proc P_TEST @TxtIntoW varchar(20)
as
SELECT A.Fpass,A.FfatherOddnumber,C.IDNAME AS WEREHOUSE,
D.IDNAME AS BILL,A.Fdate,A.Fremark,
A.FwrikeMr,B.IDNo,F.FCname,
F.Fdrawing,F.Fsizespec,G.IDNAME AS UNIT,
B.FmaPrice,B.FintoQuty,B.FSum,
B.Fremark AS REMARK,E.idname AS DEPSC,
A.FdelGoNo,b.Fnumber
FROM tabAccountlistA AS A
LEFT JOIN tabAccountlistB AS B ON A.FfatherOddnumber=B.FfatherOddnumber
LEFT JOIN tabWarehouseIDS AS C ON A.Fwerehousenumber=C.IDno
LEFT JOIN tabBillCalssS AS D ON A.BILLCALSS=D.IDno
LEFT JOIN SqlDepSupCust AS E ON A.Fdeptnumber=E.idno
LEFT JOIN tabT_item AS F ON B.Fnumber=F.Fnumber
LEFT JOIN tabUnitIDS G ON F.Funit=G.IDno
WHERE A.Fpass=1 And A.FfatherOddnumber=@TxtIntoW
[解决办法]
弄成存储过程直接调用吧，好调试，也性能高
CREATE PROC xxx ( @参数 NVARCHAR(125) )
AS
SELECT A.Fpass ,
A.FfatherOddnumber ,
C.IDNAME AS WEREHOUSE ,
D.IDNAME AS BILL ,
A.Fdate ,
A.Fremark ,
A.FwrikeMr ,
B.IDNo ,
F.FCname ,
F.Fdrawing ,
F.Fsizespec ,
G.IDNAME AS UNIT ,
B.FmaPrice ,
B.FintoQuty ,
B.FSum ,
B.Fremark AS REMARK ,
E.idname AS DEPSC ,
A.FdelGoNo ,
b.Fnumber
FROM ( ( ( ( tabAccountlistA AS A
LEFT JOIN tabAccountlistB AS B ON A.FfatherOddnumber = B.FfatherOddnumber
)
LEFT JOIN tabWarehouseIDS AS C ON A.Fwerehousenumber = C.IDno
)
LEFT JOIN tabBillCalssS AS D ON A.BILLCALSS = D.IDno
)
LEFT JOIN SqlDepSupCust AS E ON A.Fdeptnumber = E.idno
)
LEFT JOIN ( tabT_item AS F
LEFT JOIN tabUnitIDS G ON F.Funit = G.IDno
) ON B.Fnumber = F.Fnumber
WHERE ( ( ( A.Fpass ) = True )
AND ( ( A.FfatherOddnumber ) = @参数 )
)
