这个存储过程怎么有两个选择SQL的呢？

[xinqq163]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[proc_getActionConsumesByUserAndTime]
@begintime datetime,
@endtime datetime,
@username varchar(20),
@begin int,
@end int
as begin
declare @com as varchar(max)
set @com='
--if object_id(''tempdb..#actions'') is not null
--drop table #actions
--
--create table #actions
--(actionid bigint,
--nums int,
--shopid bigint,
--shopname varchar(20),
--action varchar(10),
--totalmoney decimal(16,2),
--consumerTime datetime,
--cemeteryname varchar(30),
--subuserid int,
--subusername varchar(20),
--currentmoney decimal(16,2)
--)
if object_id(''tempdb..#buynotes'') is not null
drop table #buynotes

create table #buynotes
(actionid bigint,
nums int,
shopid bigint,
shopname varchar(30),
action varchar(10),
totalmoney decimal(16,2),
consumerTime datetime,
cemeteryname varchar(30),
subuserid int,
subusername varchar(20),
currentmoney decimal(16,2),
unit varchar(10)
)
if object_id(''tempdb..#combine'') is not null
drop table #combine

create table #combine
(
actionid bigint,
nums int,
shopid bigint,
shopname varchar(30),
action varchar(10),
totalmoney decimal(16,2),
consumerTime datetime,
cemeteryname varchar(30),
subuserid int,
subusername varchar(20),
currentmoney decimal(16,2),
unit varchar(10)
)
--insert into #actions
--select actionconsumecc.actionid, sum(actionconsumecc.nums) nums,actionconsumecc.shopid,actionconsumecc.shopname,
--''祭祀'',actionconsumec.totalmoney,actionconsumec.consumerTime,dbo.cemeterys.cemeteryname,
--actionconsumec.subuserid,actionconsumec.subusername from actionconsumecc inner
-- join actionconsumec on actionconsumec.actionid=actionconsumecc.actionid inner join
--dbo.cemeterys on actionconsumec.cemeteryid=dbo.cemeterys.cemeterysid and actionconsumec.hillid=dbo.cemeterys.hill
--where actionconsumec.consumerTime>='''+convert(varchar(10),@begintime,121)+''' and
--actionconsumec.consumerTime<'''+convert(varchar(10),dateAdd(dd,1,@endtime),121)+''' and
--actionconsumec.username='''+Cast(@username as varchar(20))+'''
--group by actionconsumecc.actionid,actionconsumecc.shopid,actionconsumecc.shopname,actionconsumec.totalmoney,
--actionconsumec.consumerTime,dbo.cemeterys.cemeteryname,actionconsumec.subuserid,actionconsumec.subusername

insert into #buynotes
select -1 actionid,nums,shopid,shopname,buytype,buyprice totalmoney,buytime,dbo.cemeterys.cemeteryname,
buynote.subuserId,heavensubuser.subusername,currentmoney,buynote.unit from buynote join heavenusers on heavenusers.uid=buynote.userid
inner join heaven_bbs.dbo.heaven_users b on b.uid=heavenusers.uid left outer join
dbo.cemeterys on dbo.cemeterys.cemeterysid=buynote.tartgetid left outer join
heavensubuser on heavenusers.uid=heavensubuser.userid and buynote.subuserid>0 where
b.username='''+@username+''' and buytime>='''+convert(varchar(10),@begintime,121)+'''
and buytime<'''+convert(varchar(10),dateadd(dd,1,@endtime),121)+'''

insert into #combine
--select * from #actions
--union
select * from #buynotes

select count(*) from #combine
select * from (select *,row_number() over(order by consumerTime desc) cid from #combine)
moneying where moneying.cid>'''+Cast(@begin as varchar(10))+''' and
moneying.cid<='''+cast(@end as varchar(10))+''' order by consumerTime desc,action desc
'
exec(@com)

end


最近看公司的代码 好晕呀。
创建临时表#buynotes和#combine ，然后插入数据。 但返回的怎么读取的呢，这个有两个 都不知道怎么用的呢

还这个不明白 --where actionconsumec 前面加上 --是不是注释掉的意思呢？？ 请高手帮忙谢谢/

[Rock870210]

打开sql的查询分析器，--是不是注释就一目了然了
关于读取，看你查询的内容了，reader的时候还是根据临时表的列名来的。慢慢试吧，自己试出来的记忆更深刻

[myhope88]

人家是返回多个结果，本来就可以的啊，程序也是可以调用的啊

[zhangdoublepan]

慢慢试吧，自己试出来的记忆更深刻

[lchy110]

可以返回多个table 用 DataReader dr = cmd.ExecuteReader();
dr.Read()循环读一个结果集 然后再
dr.NextResult();读下一个。。。。。

[xinqq163]

try
{
DataSet ds = Provider.ExcuteDataset("proc_getActionConsumesByUserAndTime", Parameters, CommandType.StoredProcedure);
Actionconsume Actionconsume = null;
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
records = int.Parse(ds.Tables[0].Rows[0][0].ToString());这个是读取第一临时表的把 就是#buynotes
if (ds.Tables.Count > 1 && ds.Tables[1].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[1].Rows)
{
Actionconsume = new Actionconsume();
Actionconsume.Actionid = Int32.Parse(row["actionid"].ToString());
Actionconsume.CemeteryName = row["cemeteryname"].ToString();
Actionconsume.ShopName = row["shopname"].ToString();
Actionconsume.Num = int.Parse(row["nums"].ToString());
Actionconsume.Totalmoney = Decimal.Parse(row["totalmoney"].ToString());
Actionconsume.ConsumerTime = row["consumerTime"].ToString();
Actionconsume.ActionType = row["action"].ToString();
Actionconsume.CurrentMoney = decimal.Parse(row["currentmoney"].ToString());
Actionconsume.Unit = row["unit"].ToString();
Actionconsume.Subuserid = row["subuserid"] == null ? 0 : int.Parse(row["subuserid"].ToString());
Actionconsume.Subusername = row["subusername"] == null ? "" : row["subusername"].ToString();
list.Add(Actionconsume);
}
}
}

好象有点理解了，还要继续理解， 慢慢试吧，自己试出来的记忆更深刻 。测试真麻烦呀。

ds.Tables[0] 这个是读取第一个临时表的把 就是#buynotes了
ds.Tables[1] 这个是读取第二个临时表的把 就是#combine了

明天在来看看 就结分了。。。。。。。。

[wuyq11]

insert into #combine
select * from #buynotes
读出的数据是从 #combine临时表获取的

[pigHead_chen]