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

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

-- =============================================
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

/**//* 生成返回 */
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

<xmldata>
44

<action id="2" />
45

<query gid="-774702857" stime="2007-04-11" etime="2007-04-11"/>
46

</xmldata>
47

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

-- =============================================
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

-- 参数定义
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

-- 生成返回
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

-- 查询条件 --
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

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

posted @ 2011-12-06 09:56 Areas 阅读(399) 评论(0) 编辑收藏举报

刷新页面返回顶部