现在有 表
a 1 100
a 2 200
b 1 100
b 2 200
b 3 300
通过
declare @t table(a varchar(20),id1 int,id2 int)
insert into @t
select 'a',1,100
union all
select 'a',2,200
union all
select 'b',1,100
union all
select 'b',2,200
union all
select 'b',3,300 SELECT a,
STUFF((SELECT ','+cast(id1 as varchar)+','+ cast(id2 as varchar) AS [text)]
FROM @t AS G2
WHERE G2.a = G1.a
ORDER BY [id1]
FOR XML PATH('')),1,1,'') AS string
FROM @t AS G1
GROUP BY a;
可以得到临时表@t
a 1,100,2,200
b 1,100,2,200,3,300但是现在我想在存储过程中调用这个临时表@t得到@t相同结果,怎么样实现?
因为我要在C#中调用存储过程的结果。
希望大家帮个忙
a 1 100
a 2 200
b 1 100
b 2 200
b 3 300
通过
declare @t table(a varchar(20),id1 int,id2 int)
insert into @t
select 'a',1,100
union all
select 'a',2,200
union all
select 'b',1,100
union all
select 'b',2,200
union all
select 'b',3,300 SELECT a,
STUFF((SELECT ','+cast(id1 as varchar)+','+ cast(id2 as varchar) AS [text)]
FROM @t AS G2
WHERE G2.a = G1.a
ORDER BY [id1]
FOR XML PATH('')),1,1,'') AS string
FROM @t AS G1
GROUP BY a;
可以得到临时表@t
a 1,100,2,200
b 1,100,2,200,3,300但是现在我想在存储过程中调用这个临时表@t得到@t相同结果,怎么样实现?
因为我要在C#中调用存储过程的结果。
希望大家帮个忙
解决方案 »
- 用log explorer恢复数据遇到问题
- 简单问题 都不好意思问。。。
- 求《Microsoft SQL Server 2000 DTS Step by Step》的光盘
- 存储过程如何返回值呢?
- 55555555555555555555
- 我想用VB一个连接SQL SERVER数据库,此数据库在另一台机器(服务器)上,请问如何设置数据源等?本机要不要安装SQL SERVER ?谢谢!
- sql语句参数问题
- 从外部导入了一个表,字是简体的,我们数据库上的OS是繁体的,怎么让数据不显示乱码??????
- 如何在ODBC数据源管理器里建立一个SQL SERVER数据源
- 如何写这个sql语句?(也许这对你很简单)
- 关于sqlserver中两张表的数据导入问题
- 如何用存储过程实现两表之间数据插入
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOALTER function CalcOneBureauLineExe(@subBureauID int,@datebgn char(12),@dateend char(12))
returns @result table
(
button_id char(12),
pointid int ,
dev_id int ,
bureau_id int ,
line_id int ,
lname varchar(50),
linebelong int ,
clicktime char(10),
lstart varchar(50),
lend varchar(50)
)
AS
begin insert into @result select d.button_id, tp.id, min(d.id) as id,d.bureau_id,l.id,min(l.name),l.belongto,max(nd.time) as time ,l.pt_begin,l.pt_end
from dev d
inner join linedev ld
on d.id=ld.devid and d.bureau_id=ld.bureauid inner join line l
on l.id=ld.lineid and l.del_tag=0 and l.bureau_id=ld.bureauid
inner join toppoint tp
on tp.infoid=d.id and tp.bureauid=d.bureau_id and d.del_tag=0 left join nudity_data nd
on nd.button_id=d.button_id and nd.time>@datebgn and nd.time<@dateend where d.bureau_id=@subbureauId
group by d.bureau_id,d.button_id,l.belongto,l.id,tp.id,l.pt_begin,l.pt_end order by l.belongto,l.id return
end;
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO改过即可以