工作日志之 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