工作日志之 SQL 存储过程返回XML数据集

新公司,新工作试着用sql 存储过程写调用,所有结果都返回XML数据集,这是第一个成品,贴出来以做留念.

 

--  =============================================
--  Author:        <tanke>
--  Create date: <2007-4-17>
--  Description:    <统计访问量和综合浏览量>
--  exec sp_sys_Master '<?xml version="1.0" encoding="unicode" ?><report action="3" gid="-774702857" etime="2007-4-15" stime="2007-4-10" />'
--  =============================================
ALTER   PROCEDURE   [ dbo ] . [ SP_GetData_BrowseVisit ]
(
@hDoc   int
)
as
Begin
     --  参数定义
     declare   @stime   datetime , @etime   datetime , @type   nvarchar ( 30 ), @gid   int
     declare   @tmptab   table (cid  int ,timeid  int ,the_date  datetime ,asc_code  int )
     --  写入临时表视图
     INSERT   INTO   @tmptab (cid,timeid,the_date,asc_code)
     SELECT  c.id,b.id, b.the_date,c.asc_code
     FROM  dbo.Fact_PageAccess  AS  a,dbo.Dim_Date  AS  b,dbo.Dim_Company  AS  c
     WHERE  a.timeid  =  b.id  AND  a.cid  =  c.id

     /**/ /* 生成返回 */
     SELECT    @stime = stime, @etime = etime, @gid = gid  from  openxml( @hDoc , ' //report ' , 1 )  WITH  (stime  datetime ,etime  datetime ,gid  int )
     --  查询
     SELECT   1   AS  tag, null   AS  parent, ' accountsum/browsesum '   as   [ report!1!type ] , @gid   as   [ report!1!gid ] , convert ( varchar ( 30 ), @stime , 111 )  as   [ report!1!stime ] , convert ( varchar ( 30 ), @etime , 111 )  as   [ report!1!etime ] , null   as   [ detail!2!date ] , null   as   [ detail!2!count ] , null   as   [ detail!2!bcount ]
     UNION   ALL
     select   2 , 1 , null , null , null , null ,the_date,  COUNT ( * )  AS   count ,
    ( SELECT   COUNT ( * )  AS  bcount  FROM  dbo.Fact_SiteAccess  AS  a  WHERE  (a.timeid  =  t.timeid  AND  a.cid = t.cid))  AS  bcount 
     FROM   @tmptab  t  WHERE  asc_code = @gid   AND  ( DATEDIFF (d,  @etime , the_date)  <=   0 )  AND  ( DATEDIFF (d,  @stime , the_date)  >=   0 )
     GROUP   BY  the_date,t.timeid,t.cid
     FOR  XML EXPLICIT
End
/**/ /*
-- 返回结果
<xmldata>
  <report type="accountsum/browsesum" stime="2006-12-01" etime="2006-12-04">
    <detail date="2006-12-01" count="" bcount=""/>
    <detail date="2006-12-01" count="" bcount=""/>
    <detail date="2006-12-01" count="" bcount=""/>
  </report>
</xmldata>

<xmldata>
  <action id="2" />
  <query gid="-774702857" stime="2007-04-11" etime="2007-04-11"/>
</xmldata>
*/

 

经过一番改进后,收获还是挺多的.

 

CREATE   PROCEDURE   [ dbo ] . [ SP_GetData_BrowseVisit ]
(
@hDoc   int
)
as
Begin
     --  参数定义
     DECLARE   @stime   nvarchar ( 10 ), @etime   nvarchar ( 10 ), @type   nvarchar ( 30 ), @gid   bigint , @sid   int , @eid   int , @cid   int

     /**/ /* 生成返回 */
     SELECT    @stime = stime, @etime = etime, @gid = gid  from  openxml( @hDoc , ' //query ' , 1 ) 
     WITH  (stime  nvarchar ( 10 ),etime  nvarchar ( 10 ),gid  bigint )
     --  查询
     SELECT   @cid = id  FROM  dbo.Dim_Company  WHERE  asc_code = @gid
     SELECT   @sid = id  FROM  dbo.Dim_Date  WHERE  the_date = @stime
     SELECT   @eid = id  FROM  dbo.Dim_Date  WHERE  the_date = @etime

     SELECT  
         1   AS  tag,
         null   AS  parent,
         ' accountsum/browsesum '   AS   [ report!1!type ] ,
         @stime   AS   [ report!1!stime ] ,
         @etime   AS   [ report!1!etime ] ,
         null   AS   [ detail!2!date ] ,
         null   AS   [ detail!2!count ] ,
         null   AS   [ detail!2!bcount ]
     UNION   ALL
     SELECT  
         2 ,
         1 ,
         null ,
         null ,
         null ,
        b.the_date,
         COUNT ( * )  count ,
         COUNT ( DISTINCT  a.sid) bcount 
     FROM  dbo.Fact_PageAccess a
     INNER   JOIN  dbo.Dim_Date b  ON  b.id = a.timeid
     WHERE  b.id >= @sid   AND  b.id <= @eid   AND  a.cid = @cid
     GROUP   BY  b.the_date
     FOR  XML EXPLICIT,root( ' xmldata ' ),type
End