存储过程问题

[a97191]

我要同时返回参数和记录和记录集，请问怎么做，我用下面总是做不好
存储过程，自己测试过，存储过程是没有问题的
create procedure mainboard_graphTypeno
@second int output,
@yyear int,
@mmonth int
as
begin
select @second=count(*) from (select *from mainboard where year(fix_date)=@yyear and month(fix_date)=@mmonth) a
inner join (select typename from mainboard where year(fix_date)<=@yyear and month(fix_date)<=@mmonth
group by typename having count(typename)>=2) b on a.typename=b.typename

select typeno,dd=(select count(1) from mainboard where typeno=a.typeno and year(fix_date)=@yyear and month(fix_date)=@mmonth and type='主板') from mainboard a where year(fix_date)=@yyear and month(fix_date)=@mmonth and type='主板' group by typeno
order by dd desc

asp文件
set cmd=server.CreateObject("adodb.command")
cmd.ActiveConnection=conn
cmd.CommandText="dbo.mainboard_graphTypeno"
cmd.CommandType=4
cmd.Prepared=true
cmd.Parameters.Append cmd.CreateParameter("@second",2,3)
cmd.Parameters.append cmd.CreateParameter("@yyear",3,1,4,yyear)
cmd.Parameters.append cmd.CreateParameter("@mmonth",3,1,4,mmonth)
'set rs=server.CreateObject("adodb.recordset")
'rs.Open cmd,,1,1
set rs=cmd.execute
rowcount=cmd.Parameters("@second").value
do while not rs.eof
....
rs.MoveNext
loop

[yaoyaomaomao]

结果集的返回可以在程序里面设置的啊,不用cmd.execute,ASP里面有没有open?

[yaoyaomaomao]

cmd.execute好象是不返回结果集的

[a97191]

可以我已经试过，如果把这句
select @second=count(*) from (select *from mainboard where year(fix_date)=@yyear and month(fix_date)=@mmonth) a
inner join (select typename from mainboard where year(fix_date)<=@yyear and month(fix_date)<=@mmonth
group by typename having count(typename)>=2) b on a.typename=b.typename
去掉，是可以返回记录集的，但我要返回一个值，不知有没有这方面的列子，同时返回参数和记录集

[yaoyaomaomao]

create procedure mainboard_graphTypeno
@second int output,
@yyear int,
@mmonth int
as
SET NOCOUNT ON (加上这个试试)

[yaoyaomaomao]

另外需要的结果集需要用select重新查询

[yaoyaomaomao]

create procedure mainboard_graphTypeno
@second int output,
@yyear int,
@mmonth int
as
select typeno,dd=(select count(1) from mainboard where typeno=a.typeno and year(fix_date)=@yyear and month(fix_date)=@mmonth and type='主板') from mainboard a where year(fix_date)=@yyear and month(fix_date)=@mmonth and type='主板' group by typeno
order by dd desc

select @second=count(*) from (select *from mainboard where year(fix_date)=@yyear and month(fix_date)=@mmonth) a
inner join (select typename from mainboard where year(fix_date)<=@yyear and month(fix_date)<=@mmonth
group by typename having count(typename)>=2) b on a.typename=b.typename

这么写试试,因为有的应用程序如DELPHI只接受第一个SELECT 返回的结果集