对于数据库的存储过程之前的专题有讲过
这里具体讲述存储过程的编写方法：
例题：有heat表和eatables两张表，分别为：
eatables
heat：protein（蛋白质），fat（脂肪），sugar（含糖量），kj（热量）,以100克为单位
1.写一个存储过程Diet，以食物重量(克)为参数，输出该重量的不同类产品中，蛋白质成分最高的食物及其蛋白质含量。输出类似如下:
exec Diet 200
由于检索内容较为复杂：
所以先写检索内容：
s
elect name,a.type,max from(select type,max(protein)*(@num/100) max from eatables,heat where heat.id = eatables.id group by type)p, eatables as a,heat as b where b.protein*(@num/100)=p.max and a.id=b.id
接着加上存储过程：
create procedure Diet( @num int ) as begin declare @name varchar(10),@type varchar(10),@max decimal(8,2) declare diet_cursor cursor for select name,a.type,max from(select type,max(protein)*(@num/100) max from eatables,heat where heat.id = eatables.id group by type)p, eatables as a,heat as b where b.protein*(@num/100)=p.max and a.id=b.id open diet_cursor fetch next from diet_cursor into @name,@type,@max while @@FETCH_STATUS=0 begin print '在' +@type+'食品中' print '每'+convert(varchar,@num)+'克'+@name+'含有的蛋白质最高，达到'+convert(varchar,@max)+'克' fetch next from diet_cursor into @name,@type,@max end close diet_cursor deallocate diet_cursor end
输入
exec Diet 200
得
2.写一个存储过程HeatReport，找出平均糖含量最高的食物及其糖含量。输出类似如下:
exec HeatReport；
先写检索过程：
select top 1 type,avg_sugar from (select type,sum(sugar)/count(1) as avg_sugar from eatables,heat where eatables.id=heat.id group by type)p order by p.avg_sugar desc
再写存储过程
create procedure HeatReport as begin declare @type varchar(10),@avg_sugar decimal(8,2) declare diet_cursor cursor for select top 1 type,avg_sugar from (select type,sum(sugar)/count(1) as avg_sugar from eatables,heat where eatables.id=heat.id group by type)p order by p.avg_sugar desc open diet_cursor fetch next from diet_cursor into @type,@avg_sugar while @@FETCH_STATUS=0 begin print'在面类，米面类，水产类食品中' print @type+'的平均糖含量最高，达到每100克食物含有'+convert(varchar,@avg_sugar)+'克' fetch next from diet_cursor into @type,@avg_sugar end close diet_cursor deallocate diet_cursor end
输入
exec HeatReport
得：
