27,579
社区成员
发帖
与我相关
我的任务
分享
create procedure GetSaleSumByEmp
--@sum(salesum) output, --输出变量 需要有类型 不能有括号在这
@sum float output,
@year int,
@month int,
@empid nchar(10)
as
begin
select @sum=sum(sale.salesum)
from sale,employee
where sale.empid=employee.empid
and year(saledate)=@year
and month(saledate)=@month
group by sale.empid
--having saleid=@saleid
end
if OBJECT_ID('employee','u') is not null drop table employee
go
if OBJECT_ID('sale','u') is not null drop table sale
go
create table employee
(
empid int
)
go
insert into employee select 1
go
create table sale
(
salesum int ,
empid int,
saledate datetime
)
go
insert into sale
select 1,1,GETDATE()
----------------------------------------
if OBJECT_ID('GetSaleSumByEmp','p') is not null drop proc GetSaleSumByEmp
go
create procedure GetSaleSumByEmp
@year int,
@month int,
@empid nchar(10),
@sum numeric(38,2) output
as
begin
select @sum=sum(sale.salesum)
from sale,employee
where sale.empid=employee.empid
and year(saledate)=@year --建议使用[表名.saledate]
and month(saledate)=@month--建议使用[表名.saledate]
group by sale.empid
--having saleid=@saleid --这里有问题,不知道楼主想要过滤的条件
end
go
---调用存储过程
declare @result numeric(38,2)
--楼主注意下 使用存储过程使用output时的语法
exec GetSaleSumByEmp 2012,6,'n',@result output
select @result
/*
---------------------------------------
1.00
(1 row(s) affected)
*/
create procedure GetSaleSumByEmp
@year int,
@month int,
@empid nchar(10),
@sum numeric(38,2) output
as
begin
select @sum=sum(sale.salesum)
from sale,employee
where sale.empid=employee.empid
and year(saledate)=@year --建议使用[表名.saledate]
and month(saledate)=@month--建议使用[表名.saledate]
group by sale.empid
--having saleid=@saleid --这里有问题,不知道楼主想要过滤的条件
end