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

最新推荐文章于 2021-04-08 17:17:30 发布

weixin_30807779

最新推荐文章于 2021-04-08 17:17:30 发布

阅读量69

点赞数

原文链接：http://www.cnblogs.com/tanke/archive/2007/05/08/739217.html

版权

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

转载于:https://www.cnblogs.com/tanke/archive/2007/05/08/739217.html

weixin_30807779

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
工作日志之 SQL 存储过程返回XML数据集

新公司,新工作试着用sql 存储过程写调用,所有结果都返回XML数据集,这是第一个成品,贴出来以做留念.1--=============================================2--Author:<tanke>3--Createdate:<2007-4-17>4--Description:&l...
复制链接

扫一扫