34,593
社区成员
发帖
与我相关
我的任务
分享
create table test (name char(1),j int)
insert into t(name,j) select 'A',100
insert into t(name,j) select 'A',200
insert into t(name,j) select 'B',320
insert into t(name,j) select 'A',100
insert into t(name,j) select 'B',90
insert into t(name,j) select 'A',460
insert into t(name,j) select 'A',510
--过程:
create PROCEDURE [dbo].[mysp]
@output1 float output,
@output2 float output
AS
BEGIN
SET NOCOUNT ON
SELECT @output1= sum(j) from t where name = 'A'
SELECT @output2= sum(j) from t where name = 'B'
return
END
--调用
declare @t1 float,@t2 float
set @t1=0.0
set @t2=0.0
exec mysp @t1,@t2
select @t1,@t2
--结果:
----------------------------------------------------- -----------------------------------------0.0 0.0
(1 row(s) affected)
ALTER PROCEDURE [dbo].[mysp]
@output1 float output,
@output2 float output,
@output3 float output,
AS
BEGIN
SELECT @output1=sum(case 服务部 when 1 then 成交金额 else 0 end),
@output2=sum(case 服务部 when 2 then 成交金额 else 0 end),
@output3=sum(case 服务部 when 3 then 成交金额 else 0 end)
from 清算流水
END
declare @t1 float,@t2 float
set @t1=0.0
set @t2=0.0
exec mysp @t1 output,@t2 output
select @t1,@t2