declare @text char(100)
declare sear_cur scroll cursor for select a.customerName,a.address,sum(b.orderSum) from Customer62 a,OrderMaster62 b where a.customerNo=b.customerNo and a.customerNo=@cusno group by a.customerName,a.address select @text='===================客户订单表====================' print @text select @text='-------------------------------------------------' print @text open sear_cur
fetch sear_cur into @cusname,@address,@total while(@@fetch_status=0) begin select @text='客户名称:'+' '+@cusname print @text select @text='客户地址:'+' '+@address print @text select @text='总金额: '+' '+convert(char(12),@total) print @text fetch sear_cur into @cusname,@address,@total end select @text='-------------------------------------------------' close sear_cur deallocate sear_cur
declare @productno char(10),@num int,@avg numeric(9,2) declare searpro_cur scroll cursor for select productNo,sum(quantity),avg(price) from OrderMaster62 a,OrderDetail62 b where a.orderNo=b.orderNo and a.customerNo='C20050001' group by productNo select @text='商品编号 总数量 平均价格' print @text open searpro_cur
fetch searpro_cur into @productno,@num,@avg while(@@fetch_status=0) begin select @text=@productno+' '+convert(char(8),@num)+' +convert(char(12),@avg) print @text fetch searpro_cur into @productno,@num,@avg end
select @text='报表制作人杨学森 制作日期 2012-12-1' print @text
' close searpro_cur deallocate searpro_cur /*执行过程*/
exec proSearchCustomer C20050001