用ASP调用存储过程返回临时表记录集的一个示例代码

以下是ASP代码：
<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>




<%CheckUserPower "100009",Session("EmpPower")%>
<%
dim deptname,empname,fyear
deptname=Trim(Request("deptname"))
empname=Trim(Request("empname"))
fyear = Trim(Request("fyear"))
if fyear = "" then
fyear = 2006
end if
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
conn.open connstr
’if deptname = "" and empname ="" then
’sql = "HD_CK_XSHZ "&fyear&""
’conn.execute(sql)
’end if
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>销售出库表.</title>
<link href="../style.css" rel="stylesheet" type="text/css">
<script language="javascript">
           function exportExcel(hd_zlb){
            if (typeof(EXPORT_OBJECT)!="object"){
document.body.insertAdjacentHTML("afterBegin","<OBJECT style=’display:none’ classid=clsid:0002E510-0000-0000-C000-000000000046 id=EXPORT_OBJECT></Object>");
   }
with (EXPORT_OBJECT){
          DataType = "HTMLData";
          HTMLData =hd_zlb.outerHTML;
      try{
           ActiveSheet.Export("C:\\华达销售出库一览表.xls", 0);
  alert(’成功导出EXCEL表格！’);
            }
  catch (e){
               alert(’导出EXCEL表格失败，请确定已安装Excel2000(或更高版本),并且没打开同名xls文件’);
  }
            }
}
</script>
</head>
<body>
  <table width="100%" border="0" cellspacing="0" cellpadding="0">
<form name="form1" method="post" action="list_loading.asp">
    <tr>
      <td><select name="fyear">
        <option value="2005" <%if fyear=2005 then response.write"selected"%>>2005年</option>
        <option value="2006" <%if fyear=2006 then response.write"selected"%>>2006年</option>
        <option value="2007" <%if fyear=2007 then response.write"selected"%>>2007年</option>
      </select>

<input type="submit" name="Submit" value="查询"></td>
      <td>
           <input type="hidden" name="filename" value="list_sale_out.asp">
</td>
      <td> <div align="right">
          <input name="button" type="button" onClick="exportExcel(hd_zlb)" value="导出EXCEL表格...">
        </div></td>
    </tr>
</form>
  </table>
<%
function formatcur(cur,v)
if v=0 then
    if cur=0 then
formatcur="<font color=’#cccccc’>-</font>"
else
v9=cur&""
va=len(v9)
if va<4 then
v2=v9
else
yi=Fix(va/3)
y2=(yi+1)*3
t1=3-(y2-va)+1
if yi<>va/3 then
e2=""
for i=1 to yi
v2=","&Mid(v9,t1+3*(i-1),3)
e2=e2+v2
next
v2=Mid(v9,1,(va-3*yi))&e2
else
e2=""
for y=2 to yi
v2=","&Mid(v9,(y-1)*3+1,3)
e2=e2+v2
next
v2=Mid(v9,1,3)&e2
end if
end if
formatcur=v2
end if
else
    if cur=0 then
formatcur="<font color=’#cccccc’>-</font>"
else
formatcur=FormatCurrency(cur)
end if
end if
end function
response.write fyear
Set Cmd=server.CreateObject("Adodb.Command")
Cmd.ActiveConnection=conn
Cmd.CommandText="HD_CK_XSHZ"
Cmd.CommandType=4
cmd.prepared=true
Cmd.Parameters.Append Cmd.CreateParameter("fyear",adInteger,1,2,fyear)
Cmd.Parameters.Append Cmd.CreateParameter("deptname",adVarChar,adParamInput,50,""&deptname&"")
Cmd.Parameters.Append Cmd.CreateParameter("empname",adVarChar,adParamInput,50,""&empname&"")
set rs=Cmd.execute
if deptname<>"" then response.write "<font color=red>你查询的 "&fyear&"年度 "&deptname&" >> "&empname&" 的相关销售出库信息如下:</font>"
%>
<table width="100%" border="0" cellpadding="2" cellspacing="1" bgcolor="#999999" id="hd_zlb"  style="word-break: break-all; border-collapse:collapse">
  <tr>
    <td bgcolor="#666666"><font color="#FFFFFF">ID</font></td>
    <td bgcolor="#666666"><font color="#FFFFFF" >公司名称</font></td>
    <%if deptname="" then%>
    <td bgcolor="#666666"><font color="#FFFFFF">销售部门</font></td>
    <%end if
if empname="" then%>
    <td bgcolor="#666666"><font color="#FFFFFF">业务员</font></td>
    <%end if%>
    <td nowrap bgcolor="#666666"><font color="#FFFFFF">合计</font></td>
    <td nowrap bgcolor="#666666"><font color="#FFFFFF">1月</font></td>
    <td nowrap bgcolor="#666666"><font color="#FFFFFF">2月</font></td>
    <td nowrap bgcolor="#666666"><font color="#FFFFFF">3月</font></td>
    <td nowrap bgcolor="#666666"><font color="#FFFFFF">4月</font></td>
    <td nowrap bgcolor="#666666"><font color="#FFFFFF">5月</font></td>
    <td nowrap bgcolor="#666666"><font color="#FFFFFF">6月</font></td>
    <td nowrap bgcolor="#666666"><font color="#FFFFFF">7月</font></td>
    <td nowrap bgcolor="#666666"><font color="#FFFFFF">8月</font></td>
    <td nowrap bgcolor="#666666"><font color="#FFFFFF">9月</font></td>
    <td nowrap bgcolor="#666666"><font color="#FFFFFF">10月</font></td>
    <td nowrap bgcolor="#666666"><font color="#FFFFFF">11月</font></td>
    <td nowrap bgcolor="#666666"><font color="#FFFFFF">12月</font></td>
  </tr>
  <%
  dim s,totals
  dim m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12
  m1=0
  m2=0
  m3=0
  m4=0
  m5=0
  m6=0
  m7=0
  m8=0
  m9=0
  m10=0
  m11=0
  m12=0
  totals=0
  s=0
  while not rs.eof
  m1=m1+rs("m1")
  m2=m2+rs("m2")
  m3=m3+rs("m3")
  m4=m4+rs("m4")
  m5=m5+rs("m5")
  m6=m6+rs("m6")
  m7=m7+rs("m7")
  m8=m8+rs("m8")
  m9=m9+rs("m9")
  m10=m10+rs("m10")
  m11=m11+rs("m11")
  m12=m12+rs("m12")
  totals=totals+rs("total")
  if instr(FormatSQLstr(session("deptpower")),trim(rs("fdeptname")))>0 then
  %>
  <tr bgcolor="#FFFFFF">
    <td><nobr><%=s%></nobr></td>
    <td><nobr><%=rs("fcompanyname")%></nobr></td>
    <%if deptname="" then%>
    <td nowrap><nobr><%=rs("fdeptname")%></nobr></td>
    <%end if
if empname="" then%>
    <td nowrap><nobr><%=rs("fempname")%></nobr></td>
    <%end if%>
    <td nowrap bgcolor="#efefef">
      <div align="right"><font color="#FF0000"><nobr><%=formatcur(rs("total"),0)%></nobr></font></div></td>
    <td nowrap><div align="right"><nobr><%=formatcur(rs("m1"),0)%></nobr></div></td>
    <td nowrap bgcolor="#efefef"> <div align="right"><nobr><%=formatcur(rs("m2"),0)%></nobr> </div></td>
    <td nowrap><div align="right"><nobr><%=formatcur(rs("m3"),0)%></nobr> </div></td>
    <td nowrap bgcolor="#efefef"> <div align="right"><nobr><%=formatcur(rs("m4"),0)%></nobr> </div></td>
    <td nowrap><div align="right"><nobr><%=formatcur(rs("m5"),0)%></nobr></div></td>
    <td nowrap bgcolor="#efefef"> <div align="right"><nobr><%=formatcur(rs("m6"),0)%></nobr> </div></td>
    <td nowrap><div align="right"><nobr><%=formatcur(rs("m7"),0)%></nobr> </div></td>
    <td nowrap bgcolor="#efefef"> <div align="right"><nobr><%=formatcur(rs("m8"),0)%></nobr> </div></td>
    <td nowrap><div align="right"><nobr><%=formatcur(rs("m9"),0)%></nobr></div></td>
    <td nowrap bgcolor="#efefef"> <div align="right"><nobr><%=formatcur(rs("m10"),0)%></nobr> </div></td>
    <td nowrap><div align="right"><nobr><%=formatcur(rs("m11"),0)%></nobr> </div></td>
    <td nowrap bgcolor="#efefef"> <div align="right"><nobr><%=formatcur(rs("m12"),0)%></nobr> </div></td>
  </tr>
  <%
  end if
  rs.movenext
  s=s+1
  wend
   rs.close
   set rs = nothing
   conn.close
set conn=nothing
Set Cmd = Nothing
   %>
  <tr bgcolor="#FFFFFF">
    <td colspan="2"><strong>合计:</strong></td>
    <%if deptname="" then%>
    <td></td>
    <%end if
if empname="" then%>
    <td></td>
    <%end if%>
    <td nowrap bgcolor="#efefef">
      <div align="right"><font color="#FF0000"><%=formatcur(totals,0)%></font></div></td>
    <td nowrap> <div align="right"><%=formatcur(m1,0)%></div></td>
    <td nowrap bgcolor="#efefef"> <div align="right"><%=formatcur(m2,0)%></div></td>
    <td nowrap> <div align="right"><%=formatcur(m3,0)%></div></td>
    <td nowrap bgcolor="#efefef"> <div align="right"><%=formatcur(m4,0)%></div></td>
    <td nowrap> <div align="right"><%=formatcur(m5,0)%></div></td>
    <td nowrap bgcolor="#efefef"> <div align="right"><%=formatcur(m6,0)%></div></td>
    <td nowrap> <div align="right"><%=formatcur(m7,0)%></div></td>
    <td nowrap bgcolor="#efefef"> <div align="right"><%=formatcur(m8,0)%></div></td>
    <td nowrap> <div align="right"><%=formatcur(m9,0)%></div></td>
    <td nowrap bgcolor="#efefef"> <div align="right"><%=formatcur(m10,0)%></div></td>
    <td nowrap> <div align="right"><%=formatcur(m11,0)%></div></td>
    <td nowrap bgcolor="#efefef"> <div align="right"><%=formatcur(m12,0)%></div></td>
  </tr>
</table>
</body>
</html>

以下是存储过程代码：
CREATE   PROCEDURE HD_CK_XSHZ
(
@fyear int,
@deptname varchar(50),
@empname varchar(50)
)
AS
set nocount on
begin
--按月份、型号汇总，建立销售出库单表
create table #ck1(fyear int,fperiod int,fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),famtref numeric)
insert into #ck1
select l.fyear,l.fperiod,l.fcompanyname,l.fdeptname,fempname,sum(l.fsumamtref)
from vw_com_saleinout l where l.fyear=@fyear and  (l.fcompanyname<> ’其它客户’)
group by l.fcompanyname,l.fdeptname,l.fempname,l.fyear,l.fperiod
order by fcompanyname,fperiod
--按月份建立临时表
create table #month1(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m1 int)
insert into #month1 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=1
create table #month2(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m2 int)
insert into #month2 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=2
create table #month3(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m3 int)
insert into #month3 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=3
create table #month4(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m4 int)
insert into #month4 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=4
create table #month5(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m5 int)
insert into #month5 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=5
create table #month6(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m6 int)
insert into #month6 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=6
create table #month7(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m7 int)
insert into #month7 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=7
create table #month8(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m8 int)
insert into #month8 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=8
create table #month9(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m9 int)
insert into #month9 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=9
create table #month10(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m10 int)
insert into #month10 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=10
create table #month11(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m11 int)
insert into #month11 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=11
create table #month12(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),m12 int)
insert into #month12 select fcompanyname,fdeptname,fempname,famtref from #ck1 where fperiod=12
--建立商品销售明细一览表
create table #hd_ckhzj(fcompanyname varchar(100),fdeptname varchar(20),fempname varchar(20),Total int,
m1 int,m2 int,m3 int,m4 int,m5 int,m6 int,m7 int,m8 int,m9 int,m10 int,m11 int,m12 int)
insert into #hd_ckhzj select #ck1.fcompanyname,#ck1.fdeptname,#ck1.fempname,sum(#ck1.famtref),
isnull((select m1 from #month1  where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0),
isnull((select m2 from #month2  where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0),
isnull((select m3 from #month3  where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0),
isnull((select m4 from #month4  where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0),
isnull((select m5 from #month5  where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0),
isnull((select m6 from #month6  where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0),
isnull((select m7 from #month7  where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0),
isnull((select m8 from #month8  where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0),
isnull((select m9 from #month9  where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0),
isnull((select m10 from #month10  where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0),
isnull((select m11 from #month11  where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0),
isnull((select m12 from #month12  where fcompanyname=#ck1.fcompanyname and fdeptname=#ck1.fdeptname and fempname=#ck1.fempname),0)
from #ck1
group by fcompanyname,fdeptname,fempname
order by  fcompanyname
if (@deptname<>’’ and @empname=’’)
select * from #hd_ckhzj where fdeptname = @deptname order by total desc
else if (@deptname<>’’ and @empname<>’’)
select * from #hd_ckhzj where fdeptname = @deptname and fempname = @empname order by total desc
else
select * from #hd_ckhzj order by total desc
end

GO

将本文收藏到QQ书签与更多好友分享