一次编程实践[ASP+MSSQL]

本人工作单位是一家橡塑产品制造企业，05年2月单位领导要求我开发一个生产流程数据核对表，用来稽核流程数据的准确性，其报表基本原理如下：

后续工序今日正品数 + 后续工序今日废品数 = 本工序今日正品数 + 本工序今日留存数 - 本工序昨日留存数

了解了原理后马上开工，第一次考虑用临时表：

select distinct LEFT(productid, 4) + '00' as productid into #productid from yuancaiFlow_view
where productiondate = '2004-3-1'
and productid like '01____'
go
select a.productid,
       b.productname,
       isnull(hj.GoodQty, 0) as hj_good,
       isnull(hjtoday.qty, 0) as hj_today,
       isnull(hjyesterday.qty, 0) hj_yesterday,
       isnull(hj.GoodQty, 0) - isnull(hjtoday.qty, 0) + isnull(hjyesterday.qty, 0) - isnull(sx.GoodQty, 0) - isnull(sx.BadQty, 0) as hj_difference,
       isnull(sx.GoodQty, 0) as sx_good,
       isnull(sx.BadQty, 0) as sx_bad,
       isnull(sxtoday.qty, 0) as sx_today,
       isnull(sxyesterday.qty, 0) sx_yesterday,
       isnull(sx.GoodQty, 0) - isnull(sxtoday.qty, 0) + isnull(sxyesterday.qty, 0) - isnull(jt.GoodQty, 0) - isnull(jt.BadQty, 0) as sx_difference,
       isnull(jt.GoodQty, 0) as jt_good,
       isnull(jt.BadQty, 0) as jt_bad,
       isnull(jttoday.qty, 0) as jt_today,
       isnull(jtyesterday.qty, 0) jt_yesterday,
       isnull(jt.GoodQty, 0) - isnull(jttoday.qty, 0) + isnull(jtyesterday.qty, 0) - isnull(ph.GoodQty, 0) - isnull(ph.BadQty, 0) as jt_difference,
       isnull(ph.GoodQty, 0) as ph_good,
       isnull(ph.BadQty, 0) as ph_bad,
       isnull(phtoday.qty, 0) as ph_today,
       isnull(phyesterday.qty, 0) ph_yesterday,
       isnull(ph.GoodQty, 0) - isnull(phtoday.qty, 0) + isnull(phyesterday.qty, 0) - isnull(zj.GoodQty, 0) - isnull(zj.BadQty, 0) as ph_difference,
       isnull(zj.GoodQty, 0) as zj_good,
       isnull(zj.BadQty, 0) as zj_bad
   from #productid as a,
        finishedproduct as b,
        yuancaiFlow_view as hj,
        basflowsubsistence as hjtoday,
        basflowsubsistence as hjyesterday,
        yuancaiFlow_view as sx,
        basflowsubsistence as sxtoday,
        basflowsubsistence as sxyesterday,
        yuancaiFlow_view as jt,
        basflowsubsistence as jttoday,
        basflowsubsistence as jtyesterday,
        yuancaiFlow_view as ph,
        basflowsubsistence as phtoday,
        basflowsubsistence as phyesterday,
        yuancaiFlow_view as zj
   where a.productid = b.productid
   --hj
   and hj.productiondate = '2004-3-1'
   and left(a.productid, 4) *= left(hj.productid, 4)
   and hj.productid like '____06'  
   and left(a.productid, 4) + '06' *= hjtoday.productid
   and hjtoday.checkdate = '2004-3-1'
   and left(a.productid, 4) + '06' *= hjyesterday.productid
   and hjyesterday.checkdate = '2004-2-29'
   --sx
   and sx.productiondate = '2004-3-1'
   and left(a.productid, 4) *= left(sx.productid, 4)
   and sx.productid like '____08'  
   and left(a.productid, 4) + '08' *= sxtoday.productid
   and sxtoday.checkdate = '2004-3-1'
   and left(a.productid, 4) + '08' *= sxyesterday.productid
   and sxyesterday.checkdate = '2004-2-29'
   --jt
   and jt.productiondate = '2004-3-1'
   and left(a.productid, 4) *= left(jt.productid, 4)
   and jt.productid like '____07'  
   and left(a.productid, 4) + '07' *= jttoday.productid
   and jttoday.checkdate = '2004-3-1'
   and left(a.productid, 4) + '07' *= jtyesterday.productid
   and jtyesterday.checkdate = '2004-2-29'
   --ph
   and ph.productiondate = '2004-3-1'
   and left(a.productid, 4) *= left(ph.productid, 4)
   and ph.productid like '____09'  
   and left(a.productid, 4) + '09' *= phtoday.productid
   and phtoday.checkdate = '2004-3-1'
   and left(a.productid, 4) + '09' *= phyesterday.productid
   and phyesterday.checkdate = '2004-2-29'
   --zj
   and zj.productiondate = '2004-3-1'
   and left(a.productid, 4) *= left(zj.productid, 4)
   and zj.productid like '01__11'
go
drop table #productid
go

准备用ASP呈现报表，结果ASP端的Recordset老提示出错。只好改用函数：

-- =============================================
-- Create table function (TF)
-- =============================================
IF EXISTS (SELECT *
    FROM   sysobjects
    WHERE  name = N'fn_GetGLSubsistenceList')
 DROP FUNCTION fn_GetGLSubsistenceList
GO

CREATE FUNCTION fn_GetGLSubsistenceList
 (@CheckDate smalldatetime)
RETURNS @FlowSubsistenceList TABLE
 (ProductID nvarchar(6),
  ProductName nvarchar(32),
         HJGoodQty real,
         HJTodayQty real,
         HJYesterdayQty real,
         HJDefference real,
         SXGoodQty real,
         SXBadQty real,
         SXTodayQty real,
         SXYesterdayQty real,
         SXDefference real,
         JTGoodQty real,
         JTBadQty real,
         JTTodayQty real,
         JTYesterdayQty real,
         JTDefference real,
         PHGoodQty real,
         PHBadQty real,
         PHTodayQty real,
         PHYesterdayQty real,
         PHDefference real,
         ZJGoodQty real,
         ZJBadQty real)
AS
BEGIN
        DECLARE @ProductIDList TABLE
                (ProductID nvarchar(6))
        DECLARE @YesterdayOfCheckDate smalldatetime
        SET @YesterdayOfCheckDate = DATEADD(day, -1, @CheckDate)

        --保存该日生产的产品ID号
        INSERT @ProductIDList
        select distinct LEFT(productid, 4) + '00' as productid from yuancaiFlow_view
        where productiondate = @CheckDate
        and productid like '01____'

        --正常工序为hj->sx->jt->ph->zj
        INSERT @FlowSubsistenceList
        select a.productid,
        b.productname,
        isnull(hj.GoodQty, 0) as hj_good,
        isnull(hjtoday.qty, 0) as hj_today,
        isnull(hjyesterday.qty, 0) hj_yesterday,
        0 - (isnull(hj.GoodQty, 0) - isnull(hjtoday.qty, 0) + isnull(hjyesterday.qty, 0) - isnull(sx.GoodQty, 0) - isnull(sx.BadQty, 0)) as hj_difference,
        isnull(sx.GoodQty, 0) as sx_good,
        isnull(sx.BadQty, 0) as sx_bad,
        isnull(sxtoday.qty, 0) as sx_today,
        isnull(sxyesterday.qty, 0) sx_yesterday,
        0 - (isnull(sx.GoodQty, 0) - isnull(sxtoday.qty, 0) + isnull(sxyesterday.qty, 0) - isnull(jt.GoodQty, 0) - isnull(jt.BadQty, 0)) as sx_difference,
        isnull(jt.GoodQty, 0) as jt_good,
        isnull(jt.BadQty, 0) as jt_bad,
        isnull(jttoday.qty, 0) as jt_today,
        isnull(jtyesterday.qty, 0) jt_yesterday,
        0 - (isnull(jt.GoodQty, 0) - isnull(jttoday.qty, 0) + isnull(jtyesterday.qty, 0) - isnull(ph.GoodQty, 0) - isnull(ph.BadQty, 0)) as jt_difference,
        isnull(ph.GoodQty, 0) as ph_good,
        isnull(ph.BadQty, 0) as ph_bad,
        isnull(phtoday.qty, 0) as ph_today,
        isnull(phyesterday.qty, 0) ph_yesterday,
        0 - (isnull(ph.GoodQty, 0) - isnull(phtoday.qty, 0) + isnull(phyesterday.qty, 0) - isnull(zj.GoodQty, 0) - isnull(zj.BadQty, 0)) as ph_difference,
        isnull(zj.GoodQty, 0) as zj_good,
        isnull(zj.BadQty, 0) as zj_bad
        from @ProductIDList as a,
        finishedproduct as b,
        yuancaiFlow_view as hj,
        basflowsubsistence as hjtoday,
        basflowsubsistence as hjyesterday,
        yuancaiFlow_view as sx,
        basflowsubsistence as sxtoday,
        basflowsubsistence as sxyesterday,
        yuancaiFlow_view as jt,
        basflowsubsistence as jttoday,
        basflowsubsistence as jtyesterday,
        yuancaiFlow_view as ph,
        basflowsubsistence as phtoday,
        basflowsubsistence as phyesterday,
        yuancaiFlow_view as zj
        where a.productid = b.productid
        --hj
        and hj.productiondate = @CheckDate
        and left(a.productid, 4) *= left(hj.productid, 4)
        and hj.productid like '____06'  
        and left(a.productid, 4) + '06' *= hjtoday.productid
        and hjtoday.checkdate = @CheckDate
        and left(a.productid, 4) + '06' *= hjyesterday.productid
        and hjyesterday.checkdate = @YesterdayOfCheckDate
        --sx
        and sx.productiondate = @CheckDate
        and left(a.productid, 4) *= left(sx.productid, 4)
        and sx.productid like '____08'  
        and left(a.productid, 4) + '08' *= sxtoday.productid
        and sxtoday.checkdate = @CheckDate
        and left(a.productid, 4) + '08' *= sxyesterday.productid
        and sxyesterday.checkdate = @YesterdayOfCheckDate
        --jt
        and jt.productiondate = @CheckDate
        and left(a.productid, 4) *= left(jt.productid, 4)
        and jt.productid like '____07'  
        and left(a.productid, 4) + '07' *= jttoday.productid
        and jttoday.checkdate = @CheckDate
        and left(a.productid, 4) + '07' *= jtyesterday.productid
        and jtyesterday.checkdate = @YesterdayOfCheckDate
        --ph
        and ph.productiondate = @CheckDate
        and left(a.productid, 4) *= left(ph.productid, 4)
        and ph.productid like '____09'  
        and left(a.productid, 4) + '09' *= phtoday.productid
        and phtoday.checkdate = @CheckDate
        and left(a.productid, 4) + '09' *= phyesterday.productid
        and phyesterday.checkdate = @YesterdayOfCheckDate
        --zj
        and zj.productiondate = @CheckDate
        and left(a.productid, 4) *= left(zj.productid, 4)
        and zj.productid like '01__11'
        RETURN
END
GO

ASP端与SQL端意外地解耦合了：

Dim rsCheckList
Set rsCheckList = Server.CreateObject("ADODB.Recordset")
rsCheckList.ActiveConnection = oConn
rsCheckList.Open"select * from dbo.fn_GetGLSubsistenceList('"&datCheckDate&"')"

这次编程实践让我得到一个ASP开发的新思路：
用视图+函数封装数据库端，ASP端只调用函数，这样做最大的好处是平台移植更加方便了，并且对ASP程序员的数据库知识要求也降低了。