create procedure GetSaleSumByEmp
@sum(salesum) 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)[email protected]
and month(saledate)[email protected]
group by sale.empid
having [email protected]
end
消息 102，级别 15，状态 1，过程 GetSaleSumByEmp，第 2 行
'(' 附近有语法错误。
消息 137，级别 15，状态 1，过程 GetSaleSumByEmp，第 8 行
必须声明标量变量 "@sum"。
怎么改啊？帮帮忙吧
------解决方案--------------------
- SQL code
create procedure GetSaleSumByEmp@year int,@month int,@empid nchar(10),@sum numeric(38,2) outputasbegin select @sum=sum(sale.salesum) from sale,employee where sale.empid=employee.empid and year(saledate)[email protected] --建议使用[表名.saledate] and month(saledate)[email protected][表名.saledate] group by sale.empid --having [email protected] --这里有问题，不知道楼主想要过滤的条件 end
------解决方案--------------------
- SQL code
if OBJECT_ID('employee','u') is not null drop table employeegoif OBJECT_ID('sale','u') is not null drop table salegocreate table employee( empid int )goinsert into employee select 1gocreate table sale( salesum int , empid int, saledate datetime )goinsert into saleselect 1,1,GETDATE()----------------------------------------if OBJECT_ID('GetSaleSumByEmp','p') is not null drop proc GetSaleSumByEmpgo create procedure GetSaleSumByEmp@year int,@month int,@empid nchar(10),@sum numeric(38,2) outputasbegin select @sum=sum(sale.salesum) from sale,employee where sale.empid=employee.empid and year(saledate)[email protected] --建议使用[表名.saledate] and month(saledate)[email protected][表名.saledate] group by sale.empid --having [email protected] --这里有问题，不知道楼主想要过滤的条件 endgo---调用存储过程declare @result numeric(38,2)--楼主注意下 使用存储过程使用output时的语法exec GetSaleSumByEmp 2012,6,'n',@result outputselect @result/*---------------------------------------1.00(1 row(s) affected)*/
------解决方案--------------------
- SQL code
create procedure GetSaleSumByEmp[email protected](salesum) output, --输出变量 需要有类型 不能有括号在这@sum float output,@year int,@month int,@empid nchar(10)asbeginselect @sum=sum(sale.salesum)from sale,employeewhere sale.empid=employee.empidand year(saledate)[email protected]and month(saledate)[email protected]group by sale.empid--having [email protected]end