None.gif 两个很常用的存储过程  
None.gif
1  用于产生10条评论数据
None.gif
SET  QUOTED_IDENTIFIER  ON  
None.gif
GO
None.gif
SET  ANSI_NULLS  ON  
None.gif
GO
None.gif
ExpandedBlockStart.gifContractedBlock.gif
/**/ /*存储过程,用于产生10条评论数据*/
None.gif
None.gif
--  -- -- -- delete from reviews where operateID>15
None.gif--
 -- -- -- 
None.gif--
 -- -- -- delete from contentinfos where ObjectType=5
None.gif

None.gif
None.gif
ALTER         proc  Create100Comments
None.gif
as  
None.gif
declare   @i   int
None.gif
declare   @ContentID   bigint  
None.gif
set   @i = 1
None.gif
while   @i <= 10
None.gif
begin
None.gif
None.gif        
BEGIN   TRAN
None.gif         
if @@error   !=   0  )
None.gif            
goto  ErrorHandler
None.gif        
commit   tran
None.gif
None.gif    
INSERT   INTO   [ ContentInfos ]
None.gif        ( CategoryID,Source,
None.gif                    ObjectType,ClickCount,
None.gif                    ReviewCount,Grade,
None.gif                    VoteCount,CommendCount,
None.gif                    AuthorID,BlogID,
None.gif                    Size,CollectionTime,
None.gif                    IsActive,Keyword,
None.gif                    CollectionUser,OriginalID,
None.gif                    OriginalURL,F1,
None.gif                    F2,F3,
None.gif                    F4)
None.gif    
VALUES
None.gif        ( 
15 , '' ,
None.gif                    
5 , 100 ,
None.gif                    
100 , 1000 ,
None.gif                    
100 , 99 ,
None.gif                    
54 , 204562 ,
None.gif                    
10000 , getdate (),
None.gif                    
0 , '' ,
None.gif                    
' testuser ' , 999 ,
None.gif                    
' testurl ' , 100 ,
None.gif                    
100 , '' ,
None.gif                    
'' )
None.gif
None.gif    
SET   @ContentID   =  IDENT_CURRENT( ' ContentInfos ' )
None.gif    
INSERT   INTO   Reviews (CellID ,  PortalID ,  UserID ,  ContentID ,  OperateTitle ,  OperateContent ,  OperateDateTime ,  BlogID ,  BlogTitle ,  OperateUserID ,  UserName ,  UserNick ,  CBlogName ,  OperateEmail ,  OperateIP ,  IsAnonymous ,  IsActive ,  OperateHomePage ,  OperateType ,  OperateValue )
None.gif        
VALUES ( @i @i @i @ContentID ' 操作标题 ' ' 评论内容1<br>评论内容2<br>评论内容1<br><br><br><br>评论内容2<br><br><br><br><br>评论内容2<br><br><br><br><br>评论内容4<br> ' getdate (),  @i ' Blog标题 ' @i ' 用户名称 ' ' 用户呢称 ' ' Blog名称 ' ' [email]test@126.com[/email] ' ' 124.458.135.500 ' 0 1 ' [url]http://www.blogcn.com[/url] ' 1 @i )
None.gif
None.gif
None.gif            
None.gif    
if @@error   !=   0  )
None.gif        
goto  ErrorHandler
None.gif
None.gifErrorHandler:
None.gif    
if @@error   !=   0  )
None.gif    
begin
None.gif        
rollback   tran
None.gif    
end
None.gif
set   @i = @i + 1
None.gif
None.gif
end
None.gif
None.gif
GO
None.gif
SET  QUOTED_IDENTIFIER  OFF  
None.gif
GO
None.gif
SET  ANSI_NULLS  ON  
None.gif
GO
None.gif
None.gif
2  产生分页的存储过程 
None.gif
None.gif
None.gif
None.gif
None.gif
None.gif
None.gif
SET  QUOTED_IDENTIFIER  ON  
None.gif
GO
None.gif
SET  ANSI_NULLS  ON  
None.gif
GO
None.gif
None.gif
None.gif
ExpandedBlockStart.gifContractedBlock.gif
/**/ /****** Object:  Stored Procedure dbo.CPP_GetTheLastSchoolsWithPage   Script Date: 2006-5-11 10:40:08 ******/
None.gif
None.gif
ExpandedBlockStart.gifContractedBlock.gif
/**/ /* ##SUMMARY 查询一个学校的话题或者活动等 */
None.gif
None.gif
--  ##REMARKS Authors :wht    Date:2006-6-5
None.gif--
 ##PARAM @PageSize     页大小          整型INT
None.gif--
 ##PARAM @PageIndex    页索引       整型INT
None.gif--
 ##PARAM @RowCount     总记录数          整型INT
None.gif

None.gif
None.gif
None.gif
-- CPP_GetSchoolThemeWithPageNew 10,0,100
None.gif

None.gif
None.gif
create   procedure  CPP_Getthesis_thesisWithPageNew
None.gif(
None.gif   
@PageSize         INT ,
None.gif   
@PageIndex        INT ,
None.gif   
@RowCount         INT
None.gif   
-- --@whereClauses varchar(1000)
None.gif
)
None.gif
None.gif
AS
None.gif
None.gif
DECLARE   @SQL   VARCHAR ( 5000 )
None.gif
declare   @PageCount   int
None.gif
declare   @currentPageSize    int  
None.gif
None.gif
None.gif
None.gif
None.gif
None.gif
-- 计算总页数
None.gif
SET   @PageCount = CASE   WHEN   @RowCount % @PageSize = 0   THEN   @RowCount / @PageSize   ELSE   @RowCount / @PageSize + 1   END
None.gif
None.gif
SET   @PageIndex = @PageIndex + 1
None.gif
None.gif
-- 第一页
None.gif
IF   @PageIndex <= 1  
None.gif
BEGIN                 
None.gif       
set   @SQL = ' select SchoolName,t.* from (SELECT top  ' + cast ( @PageSize   as   varchar ( 10 )) + '  ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname 
None.gif         FROM  School_Themes 
None.gif        
None.gif         order by id desc)t inner join school v on t.SchoolID = v.SchoolID order by id desc
'
None.gif
None.gif
END
None.gif
ELSE  
None.gif
BEGIN
None.gif    
-- 最后一页
None.gif
     IF   @PageIndex >= @PageCount   OR   @PageIndex <= 0  
None.gif        
BEGIN
None.gif            
set   @currentPageSize =   @RowCount - ( @PageCount - 1 ) * @PageSize
None.gif                        
if ( @currentPageSize <= 0 )
None.gif            
begin
None.gif                            
set   @currentPageSize = @PageSize
None.gif                        
end
None.gif
None.gif            
SET   @SQL = ' select SchoolName,t.*
None.gif                  from (SELECT top 
' + cast ( @PageSize   as   varchar ( 10 )) + ' ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
None.gif                     FROM  School_Themes
None.gif                     order by id desc)t inner join school v on t.SchoolID = v.SchoolID order by id desc
'
None.gif
None.gif        
END
None.gif    
ELSE
None.gif        
BEGIN
None.gif            
-- 中间页(上)
None.gif
             IF   @PageIndex > 1   AND   @PageIndex <= @PageCount / 2 + 1
None.gif                
BEGIN
None.gif                    
SET   @SQL = '                     
None.gif                        SELECT TOP 
' + CONVERT ( VARCHAR ( 15 ), @PageSize ) + '  SchoolName, t.*
None.gif                        FROM (
None.gif                            SELECT TOP 
' + CONVERT ( VARCHAR ( 15 ), @PageSize ) + '  ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
None.gif                            FROM (
None.gif                                SELECT TOP 
' + CONVERT ( VARCHAR ( 15 ), @pageSize * @pageIndex ) + '   ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
None.gif                                        FROM  School_Themes
None.gif                                 
None.gif                                 order by id desc              
None.gif                                  )AS A                                 
None.gif                            order by id asc    
None.gif                              )AS t  inner join school v on t.SchoolID = v.SchoolID               
None.gif                        order by id desc
'
None.gif                
END
None.gif            
ELSE
None.gif            
-- 中间页(下)
None.gif
                 BEGIN
None.gif                    
SET   @SQL = ' SELECT TOP  ' + CONVERT ( VARCHAR ( 15 ), @PageSize ) + '  shoolName, t.*
None.gif                          FROM (
None.gif                            SELECT TOP 
' + CONVERT ( VARCHAR ( 15 ), @rowCount   -   @pageSize   *   @pageIndex + @pageSize ) + '  ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
None.gif                                    FROM  School_Themes
None.gif                             
None.gif                             order by id asc    
None.gif                               )AS t    inner join school v on t.SchoolID = v.SchoolID                                            
None.gif                              order id desc    
'  
None.gif
None.gif                
END
None.gif        
END
None.gif
END
None.gif
print   @SQL
None.gif
EXEC  ( @SQL )
None.gif
None.gif
None.gif
None.gif
None.gif
GO
None.gif
SET  QUOTED_IDENTIFIER  OFF  
None.gif
GO
None.gif
SET  ANSI_NULLS  ON  
None.gif
GO