＋设为首页 ＋添加到收藏夹
create proc CAL_GPA
(
@SNO char(7)
,@GPA dec(4,2) output
)
as
select sc.sNo
,@GPA=sum((case when grade>=85 then 4 when grade<85 and grade>=75 then 3 when grade<75 and grade>=60 then 2 when grade<60 then 1 else 0 end)*c.credit)/sum(c.credit)
from sc inner join courses c on (sc.cNo=c.cNo)
where sc.sNo=@SNO
group by sc.sNo
报错:
服务器: 消息 141，级别 15，状态 1，过程 CAL_GPA，行 11
向变量赋值的 SELECT 语句不能与数据检索操作结合使用。
但是如果不给@GPA赋值就不会报错
为什么呀?-_-!???
create proc CAL_GPA
(
@SNO char(7)
,@GPA dec(4,2) output
)
as
select @GPA=sum((case when grade>=85 then 4 when grade<85 and grade>=75 then 3 when grade<75 and grade>=60 then 2 when grade<60 then 1 else 0 end)*c.credit)/sum(c.credit)
from sc inner join courses c on (sc.cNo=c.cNo)
where sc.sNo=@SNO
group by sc.sNo
假设SQL语句没有，可是你的语句要返回很多个@GPA值，你让SQL怎么做呢！
不过你可以试试这个办法：
create proc CAL_GPA
(
@SNO char(7)
,@GPA dec(4,2) output
)
as
select @GPA=(select sum((case when grade>=85 then 4 when grade<85 and grade>=75 then 3 when grade<75 and grade>=60 then 2 when grade<60 then 1 else 0 end)*c.credit)/sum(c.credit)
from sc inner join courses c on (sc.cNo=c.cNo)
where sc.sNo=@SNO
group by sc.sNo)
