sqlserver 存储过程

利用游标复制

alter procedure p2
as
begin
	declare @course char(20),
			@score int, 
			@sno int
	declare mycursor cursor  for 
	select *
	from aaa
	open mycursor
	fetch mycursor into @sno,@course,@score
	while(@@FETCH_STATUS=0)
	begin
		insert into bbb
		values(@sno,@course,@score)
		fetch mycursor into @sno,@course,@score
	end
	close mycursor
end

求平均 带参数

create procedure p3
@course char(20)
as
begin
	select avg(score) as score_avg
	from aaa
	where course=@course
end

exec p3 @course='discrete_m'

注意写while的时候用begin end

@@FETCH_STATUE=0 大写

 

case

when ...then ...

when... then ...

end