新公司,新工作试着用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>
*/
-- 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
(
@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