工作日志之 SQL 存储过程返回XML数据集
来源:互联网 发布:php就业前景 编辑:程序博客网 时间:2024/05/22 01:29
新公司,新工作试着用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
- 工作日志之 SQL 存储过程返回XML数据集
- sql存储过程新增数据+返回数据集+事务机制
- 返回xml数据的存储过程
- SQL Server在存储过程内引用返回数据集的存储过程
- SQL Server 存储过程返回记录集
- 使用存储过程返回一个数据集
- mysql 存储过程返回数据集
- PostgreSQL存储过程返回数据集实例
- PostgreSQL存储过程返回数据集实例
- 返回数据库数据的存储过程函数(SQL Server)
- VB.NET、EXCEL调用SQL Server带参数的存储过程并返回数据集
- sql 存储过程返回值
- sql 存储过程返回值
- SQL存储过程返回值
- SQL存储过程返回值
- 存储过程备份SQL日志
- 存储过程备份SQL日志
- 存储过程备份SQL日志
- 随机生成数字字母
- JavaScript KeyCode
- 关于DBNull类型的说明
- 我的实现图像简单复制的两个c程序
- Scott Ambler 谈如何编好的软件模型
- 工作日志之 SQL 存储过程返回XML数据集
- 修复SQLSERVER2000数据库之实战经验
- 软件开发商还在为产品销路犯愁吗~找我们来帮忙吧~全国最大的软件分销商
- 转:DOS批处理脚本语言简介
- delete和delete[]的区别
- NetTier模板生成的代码框架用法
- Server_Session_Timeout
- VB.net文件夹操作
- iframe+div 实现动态扩展功能