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

 1 --  =============================================
 2 --  Author:        <tanke>
 3 --  Create date: <2007-4-17>
 4 --  Description:    <统计访问量和综合浏览量>
 5 --  exec sp_sys_Master '<?xml version="1.0" encoding="unicode" ?><report action="3" gid="-774702857" etime="2007-4-15" stime="2007-4-10" />'
 6 --  =============================================
 7 ALTER   PROCEDURE   [ dbo ] . [ SP_GetData_BrowseVisit ]
 8 (
 9 @hDoc   int
10 )
11 as
12 Begin
13      --  参数定义
14      declare   @stime   datetime , @etime   datetime , @type   nvarchar ( 30 ), @gid   int
15      declare   @tmptab   table (cid  int ,timeid  int ,the_date  datetime ,asc_code  int )
16      --  写入临时表视图
17      INSERT   INTO   @tmptab (cid,timeid,the_date,asc_code)
18      SELECT  c.id,b.id, b.the_date,c.asc_code
19      FROM  dbo.Fact_PageAccess  AS  a,dbo.Dim_Date  AS  b,dbo.Dim_Company  AS  c
20      WHERE  a.timeid  =  b.id  AND  a.cid  =  c.id
21
22      /**/ /*  生成返回  */
23      SELECT    @stime = stime, @etime = etime, @gid = gid  from  openxml( @hDoc , ' //report ' , 1 )  WITH  (stime  datetime ,etime  datetime ,gid  int )
24      --  查询
25      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 ]
26      UNION   ALL
27      select   2 , 1 , null , null , null , null ,the_date,  COUNT ( * )  AS   count ,
28     ( SELECT   COUNT ( * )  AS  bcount  FROM  dbo.Fact_SiteAccess  AS  a  WHERE  (a.timeid  =  t.timeid  AND  a.cid = t.cid))  AS  bcount 
29      FROM   @tmptab  t  WHERE  asc_code = @gid   AND  ( DATEDIFF (d,  @etime , the_date)  <=   0 )  AND  ( DATEDIFF (d,  @stime , the_date)  >=   0 )
30      GROUP   BY  the_date,t.timeid,t.cid
31      FOR  XML EXPLICIT
32 End
33 /**/ /*
34 -- 返回结果
35 <xmldata>
36   <report type="accountsum/browsesum" stime="2006-12-01" etime="2006-12-04">
37     <detail date="2006-12-01" count="" bcount=""/>
38     <detail date="2006-12-01" count="" bcount=""/>
39     <detail date="2006-12-01" count="" bcount=""/>
40   </report>
41 </xmldata>
42
43 <xmldata>
44   <action id="2" />
45   <query gid="-774702857" stime="2007-04-11" etime="2007-04-11"/>
46 </xmldata>
47 */

 26 --  =============================================
 27 --  Author:        <tanke>
 28 --  Create date: <2007-4-17>
 29 --  Description:    <统计访问量和综合浏览量>
 30 --  exec sp_sys_Master '<xmldata><action id="3" /><query gid="-774702857" etime="2007-4-15" stime="2007-4-10" /></xmldata>'
 31 --  =============================================
 32 ALTER   PROCEDURE   [ dbo ] . [ SP_GetData_BrowseVisit ]
 33 (
 34 @hDoc   int
 35 )
 36 as
 37 BEGIN     
 38      --  消除多余的网络流量
 39      SET  NOCOUNT  ON
 40     
 41      --  参数定义    
 42      DECLARE   @SQL   nvarchar ( 4000 ), @stime   nvarchar ( 10 ), @etime   nvarchar ( 10 ), @type   nvarchar ( 30 ), @gid   bigint , @sid   int , @eid   int , @cid   int , @s   int , @e   int , @month   nvarchar ( 2 ), @part   int , @websiteid   int ;
 43      --  条件参数
 44      DECLARE   @frmurlid   int , @frm   nvarchar ( 30 ), @frmtypeid   int , @frmtype   nvarchar ( 30 ), @areaid   int , @area   nvarchar ( 30 ), @networkid   int , @network   nvarchar ( 30 ), @shourid   int , @ehourid   int , @keyid   int , @keyname   nvarchar ( 30 )
 45      --  临时表
 46      DECLARE   @tmptable   TABLE ( [ id ]   [ bigint ] ,     [ cid ]   [ int ] , [ sid ]   [ int ] , [ websiteid ]   int , [ pageid ]   [ bigint ] ,     [ timeid ]   [ int ] ,     [ intime ]   [ smalldatetime ]  , [ outtime ]   [ smalldatetime ]  , [ spantime ]   [ int ] , [ pagevalue ]   [ int ] , [ pvalue ]   [ int ]  , [ vorder ]   [ int ]  , [ Tag ]   [ int ] ,areaid  int ,networkid  int ,frmurlid  int ,frmtypeid  int ,keyid  int ,hourid  int )
 47     
 48      --  生成返回
 49      SELECT   @stime = stime, @etime = etime, @gid = gid, @frm = isnull (frm, '' ), @frmtype = frmtype, @area = area, @network = network, @shourid = isnull (shourid, 0 ), @ehourid = isnull (ehourid, 0 ), @keyname = isnull (keyname, ' 未知 ' )  from  openxml( @hDoc , ' //query ' , 1 )  WITH  (stime  nvarchar ( 10 ),etime  nvarchar ( 10 ),gid  bigint ,frm  nvarchar ( 30 ),frmtype  nvarchar ( 30 ),area  nvarchar ( 30 ),network  nvarchar ( 30 ),shourid  int ,ehourid  int ,keyname  nvarchar ( 30 ));
 50     
 51      --  查询条件 --
 52      --  公司ID和站点ID
 53      SELECT   @cid = cid, @websiteid = id  FROM  dbo.Dim_WebSite  WHERE  asc_code = @gid ;
 54      --  开始时间
 55      SELECT   @sid = id  FROM  dbo.Dim_Date  WHERE  the_date = @stime
 56      --  结束时间
 57      SELECT   @eid = id  FROM  dbo.Dim_Date  WHERE  the_date = @etime
 58      --  平台来源 
 59      SELECT   @frmurlid = id  FROM  Dim_ComeFrom  WHERE   [ name ] = @frm
 60      --  来源定义
 61      SELECT   @frmtypeid = id  FROM  Dim_ComeFromType  WHERE  frmtype = @frmtype
 62      --  访客所在地域 (省份或城市)
 63      SELECT   @areaid = id  FROM  Dim_Area  WHERE  city = @area   OR  province = @area
 64      --  网络提供商
 65      SELECT   @networkid = id  FROM  dim_network  WHERE  network = @network
 66      --  时间段(开始/结束)
 67      if   @shourid > 0
 68          SELECT   @shourid = @shourid + 1
 69      if   @ehourid > 0     
 70          SELECT   @ehourid = @ehourid + 1
 71      --  关键字
 72      SELECT      @keyid = id  FROM  Dim_KeyWord  WHERE  keywordname = @keyname
 73          select   @part = @cid / 200 + 1      -- -----查找分区
 74     
 75      set   @s = cast ( substring ( @stime , 6 , 2 )  as   int )
 76      set   @e = cast ( substring ( @etime , 6 , 2 )  as   int )
 77      if   @s = @e
 78          begin
 79              if   @s < 10
 80                  set   @month = ' 0 ' + cast ( @s   as   nvarchar ( 1 ))
 81              else
 82                  set   @month = cast ( @s   as   nvarchar ( 2 ))
 83              set   @SQL = ' SELECT a.*,b.areaid,b.networkid,b.frmurlid,b.frmtypeid,b.keyid,b.hourid FROM Fact_PageAccess_ ' + @month + '  a ' +
 84                   '  inner join Fact_SiteAccess_ ' + @month + '  b on a.sid=b.sid ' +
 85                   '  WHERE  a.timeid>= ' + cast ( @sid   as   nvarchar ( 10 )) + '  AND a.timeid<= ' + cast ( @eid   as   nvarchar ( 10 )) +
 86                   '  and a.websiteid= ' + cast ( @websiteid   as   nvarchar ( 10 )) +
 87                   '  AND $PARTITION.[CidRangePFN](a.cid)= ' + cast ( @part   as   nvarchar ( 3 )) +
 88                   '  AND $PARTITION.[CidRangePFN](b.cid)= ' + cast ( @part   as   nvarchar ( 3 ))
 89          end
 90      else
 91          begin
 92              set   @SQL = ' select * from ( '
 93              while   @s < @e + 1
 94                  begin
 95                      if   @s < 10
 96                          set   @month = ' 0 ' + cast ( @s   as   nvarchar ( 1 ))
 97                      else
 98                          set   @month = cast ( @s   as   nvarchar ( 2 ))
 99                      set   @SQL = @SQL +   '
100                                 SELECT a.*,b.areaid,b.networkid,b.frmurlid,b.frmtypeid,b.keyid,b.hourid from Fact_PageAccess_ ' + @month + '  a ' +
101                                  '  inner join Fact_SiteAccess_ ' + @month + '  b on a.sid=b.sid ' +
102                                  '  WHERE  a.timeid>= ' + cast ( @sid   as   nvarchar ( 10 )) + '  AND a.timeid<= ' + cast ( @eid   as   nvarchar ( 10 )) +
103                                  '  and a.websiteid= ' + cast ( @websiteid   as   nvarchar ( 10 )) +
104                                  '  AND $PARTITION.[CidRangePFN](a.cid)= ' + cast ( @part   as   nvarchar ( 3 )) +
105                                  '  AND $PARTITION.[CidRangePFN](b.cid)= ' + cast ( @part   as   nvarchar ( 3 ))
106                  if   @s < @e
107                      set   @SQL = @SQL + '  UNION ALL  '
108                  else
109                      set   @SQL = @SQL + '  ) z where 1=1 '
110                  set   @s = @s + 1
111                  end
112          END
113         
114      if   @frmurlid > 0
115          set   @SQL = @SQL + '  and frmurlid= ' + cast ( @frmurlid   as   nvarchar ( 10 ))    
116      if   @networkid > 0
117          set   @SQL = @SQL + '  and networkid= ' + cast ( @networkid   as   nvarchar ( 10 ))
118      if   @areaid > 0
119          set   @SQL = @SQL + '  and areaid= ' + cast ( @areaid   as   nvarchar ( 10 ))
120      if   @keyid > 0
121          set   @SQL = @SQL + '  and keyid= ' + cast ( @keyid   as   nvarchar ( 10 ))    
122      if   @frmtypeid > 0
123          set   @SQL = @SQL + '  and frmtypeid= ' + cast ( @frmtypeid   as   nvarchar ( 10 ))
124      if   @shourid > 0
125          set   @SQL = @SQL + '  and hourid>= ' + cast ( @shourid   as   nvarchar ( 10 ))
126      if   @ehourid > 0
127          set   @SQL = @SQL + '  and hourid<= ' + cast ( @ehourid   as   nvarchar ( 10 ))
128         
129      PRINT   @SQL
130      INSERT   INTO   @tmptable
131      EXEC ( @SQL )
132     
133      SELECT  
134          1   AS  tag,
135          null   AS  parent,
136          ' accountsum/browsesum '   AS   [ report!1!type ] ,
137          @stime   AS   [ report!1!stime ] ,
138          @etime   AS   [ report!1!etime ] ,
139          null   AS   [ detail!2!date ] ,
140          null   AS   [ detail!2!count ] ,
141          null   AS   [ detail!2!bcount ]
142      UNION   ALL
143      SELECT  
144          2 ,
145          1 ,
146          null ,
147          null ,
148          null ,
149         b.the_date,
150          COUNT ( DISTINCT  a.sid)  count , 
151          COUNT ( * ) bcount
152      FROM   @tmptable  a
153      INNER   JOIN  dbo.Dim_Date b  ON  b.id = a.timeid
154      GROUP   BY  b.the_date
155      FOR  XML EXPLICIT,root( ' xmldata ' ),type
156 End
157 /*
158 -- 返回结果
159 <xmldata>
160   <report type="accountsum/browsesum" stime="2006-12-01" etime="2006-12-04">
161     <detail date="2006-12-01" count="" bcount=""/>
162     <detail date="2006-12-01" count="" bcount=""/>
163     <detail date="2006-12-01" count="" bcount=""/>
164   </report>
165 </xmldata>
166 */

http://www.cnblogs.com/tanke/archive/2007/05/08/739217.aspx