我写的存储过程

--存储过程:获得最新10条研报信息 
---2011-11-11 修改 lp,未能取到最新的研报 
IF exists (select * from SysObjects where name='sp_reportlist' and type='p')
   drop procedure sp_reportlist
go
 
CREATE PROCEDURE sp_reportlist
@stockcode nvarchar(20)
as
if(@stockcode!='')
begin
 
SELECT top 10 T1.ReportID,T1.StockCode,T2.ReportTitle,T3.SourceID,T3.CompanyName,T3.CompanyShortName,  
CASE  
WHEN T1.Grade=0 THEN '不评级' WHEN T1.Grade=1 THEN '买入'  WHEN T1.Grade=2 THEN '增持' WHEN T1.Grade=3 THEN '减持'  
WHEN T1.Grade=4 THEN '卖出' WHEN T1.Grade=5 THEN '中性' END AS Grade, 
 CASE 
WHEN T1.GradeUpdate=0 THEN '--' WHEN T1.GradeUpdate=1 THEN '维持' WHEN T1.GradeUpdate=2 THEN '调低' 
WHEN T1.GradeUpdate=3 THEN '调高'  END AS GradeUpdate,
T1.TargetPrice,T2.ReportDate  FROM    StockReview T1,Report T2,Company T3  where (T1.stockcode=@stockcode or T1.stockname=@stockcode)  and T1.ReportID = T2.ReportID   
and T2.CompanyID = T3.SourceID    order by T2.ReportDate desc
end
else   --取全部数据的前10条
begin
SELECT top 10 T1.ReportID,T1.StockCode,T2.ReportTitle,T3.SourceID,T3.CompanyName,T3.CompanyShortName,  
CASE  
WHEN T1.Grade=0 THEN '不评级' WHEN T1.Grade=1 THEN '买入'  WHEN T1.Grade=2 THEN '增持' WHEN T1.Grade=3 THEN '减持'  
WHEN T1.Grade=4 THEN '卖出' WHEN T1.Grade=5 THEN '中性' END AS Grade, 
 CASE 
WHEN T1.GradeUpdate=0 THEN '--' WHEN T1.GradeUpdate=1 THEN '维持' WHEN T1.GradeUpdate=2 THEN '调低' 
WHEN T1.GradeUpdate=3 THEN '调高'  END AS GradeUpdate,
T1.TargetPrice,T2.ReportDate  FROM    StockReview T1,Report T2,Company T3  where T1.ReportID = T2.ReportID   
and T2.CompanyID = T3.SourceID    order by T2.ReportDate desc
end
GO
 
----存储过程:通过stockcode获得所有研报信息
-- 如果stockcode为空字符串，则返回前所有数据
IF exists (select * from SysObjects where name='sp_allreportlist' and type='p')
   drop procedure sp_allreportlist
go
CREATE PROCEDURE sp_allreportlist
@stockcode nvarchar(20)
as
  if(@stockcode!='')
begin
SELECT   T1.ReportID,T1.StockCode,T2.ReportTitle,T3.SourceID,T3.CompanyName,T3.CompanyShortName,  
CASE  
WHEN T1.Grade=0 THEN '不评级' WHEN T1.Grade=1 THEN '买入'  WHEN T1.Grade=2 THEN '增持' WHEN T1.Grade=3 THEN '减持'  
WHEN T1.Grade=4 THEN '卖出' WHEN T1.Grade=5 THEN '中性' END AS Grade, 
 CASE 
WHEN T1.GradeUpdate=0 THEN '--' WHEN T1.GradeUpdate=1 THEN '维持' WHEN T1.GradeUpdate=2 THEN '调低' 
WHEN T1.GradeUpdate=3 THEN '调高'   END AS GradeUpdate,
T1.TargetPrice,T2.ReportDate  FROM (select   ReportID,StockCode,Grade,GradeUpdate,TargetPrice from  StockReview where stockcode=@stockcode or stockname=@stockcode) T1 left join Report T2 on  T1.ReportID = T2.ReportID  left join Company T3 
on T2.CompanyID = T3.SourceID      order by T2.ReportDate desc
end
else
begin
 SELECT   T1.ReportID,T1.StockCode,T2.ReportTitle,T3.SourceID,T3.CompanyName,T3.CompanyShortName, 
 CASE   WHEN T1.Grade=0 THEN '不评级'   WHEN T1.Grade=1 THEN '买入'  WHEN T1.Grade=2 THEN '增持'  
WHEN T1.Grade=3 THEN '减持'   WHEN T1.Grade=4 THEN '卖出'  WHEN T1.Grade=5 THEN '中性' END AS Grade,  
 CASE  WHEN T1.GradeUpdate=0 THEN '--'   WHEN T1.GradeUpdate=1 THEN '维持'  WHEN T1.GradeUpdate=2 THEN '调低'  
 WHEN T1.GradeUpdate=3 THEN '调高'   END AS GradeUpdate,  T1.TargetPrice,T2.ReportDate 
FROM StockReview T1 , Report T2,Company T3  
where  T1.ReportID = T2.ReportID and T2.CompanyID = T3.SourceID     
order by T2.Reportdate desc 
 
end
 GO
 
 
---存储过程:通过stockcode获得所有研报信息的条数
IF exists (select * from SysObjects where name='sp_allreportcount' and type='p')
   drop procedure sp_allreportcount
go
CREATE PROCEDURE sp_allreportcount
@stockcode nvarchar(20)
as
  if(@stockcode!='')
begin
SELECT   count(T1.ReportID)  FROM (select   ReportID,StockCode,Grade,GradeUpdate,TargetPrice from  StockReview where stockcode=@stockcode or stockname=@stockcode) T1 left join Report T2 on  T1.ReportID = T2.ReportID  left join Company T3 
on T2.CompanyID = T3.SourceID      
end
else
begin
 SELECT count( T1.ReportID)   FROM StockReview T1 , Report T2,Company T3  
where  T1.ReportID = T2.ReportID and T2.CompanyID = T3.SourceID     
end
 GO
 
exec sp_allreportcount '000001'
 
 
exec  sp_allreportlist ''  --执行存储过程
 
 
 
exec  sp_reportpagelist '000001' ,10,10
---存储过程:分页，获得第@endrecord-@num+1条到第@endrecord条记录的研报信息
IF exists (select * from SysObjects where name='sp_reportpagelist' and type='p')
   drop procedure sp_reportpagelist
go
 
CREATE PROCEDURE sp_reportpagelist
@stockcode nvarchar(20),
@num int,   --条数
@endrecord int   --从0到该页最后一条记录的总记录数
as
if(@stockcode!='')  --如果@stockcode不为空
begin
select * from ( select top (@num) * from (
SELECT    top (@endrecord)  T1.ReportID,T1.StockCode,T2.ReportTitle,T3.SourceID,T3.CompanyName,T3.CompanyShortName,  
CASE  
WHEN T1.Grade=0 THEN '不评级' WHEN T1.Grade=1 THEN '买入'  WHEN T1.Grade=2 THEN '增持' WHEN T1.Grade=3 THEN '减持'  
WHEN T1.Grade=4 THEN '卖出' WHEN T1.Grade=5 THEN '中性' END AS Grade, 
 CASE 
WHEN T1.GradeUpdate=0 THEN '--' WHEN T1.GradeUpdate=1 THEN '维持' WHEN T1.GradeUpdate=2 THEN '调低' 
WHEN T1.GradeUpdate=3 THEN '调高'   END AS GradeUpdate,
T1.TargetPrice,T2.ReportDate  FROM 
(select  ReportID,StockCode,Grade,GradeUpdate,TargetPrice from  StockReview where stockcode=@stockcode or stockname=@stockcode) T1 
left join Report T2 on  T1.ReportID = T2.ReportID  left join Company T3 
on T2.CompanyID = T3.SourceID      order by T2.ReportDate desc
) TT  order by TT.ReportDate) TTT order by TTT.ReportDate desc 
end
else  --获得所有的研报数据
begin
select * from ( select top (@num) * from (
SELECT    top (@endrecord)  T1.ReportID,T1.StockCode,T2.ReportTitle,T3.SourceID,T3.CompanyName,T3.CompanyShortName,  
CASE  
WHEN T1.Grade=0 THEN '不评级' WHEN T1.Grade=1 THEN '买入'  WHEN T1.Grade=2 THEN '增持' WHEN T1.Grade=3 THEN '减持'  
WHEN T1.Grade=4 THEN '卖出' WHEN T1.Grade=5 THEN '中性' END AS Grade, 
 CASE 
WHEN T1.GradeUpdate=0 THEN '--' WHEN T1.GradeUpdate=1 THEN '维持' WHEN T1.GradeUpdate=2 THEN '调低' 
WHEN T1.GradeUpdate=3 THEN '调高'   END AS GradeUpdate,
T1.TargetPrice,T2.ReportDate  FROM 
(select  ReportID,StockCode,Grade,GradeUpdate,TargetPrice from  StockReview) T1 
left join Report T2 on  T1.ReportID = T2.ReportID  left join Company T3 
on T2.CompanyID = T3.SourceID      order by T2.ReportDate desc
) TT  order by TT.ReportDate) TTT order by TTT.ReportDate desc 
 
end
 GO