两个常用的存储过程

两个很常用的存储过程  
1  用于产生10条评论数据
SET  QUOTED_IDENTIFIER  ON  
GO
SET  ANSI_NULLS  ON  
GO

/**/ /*存储过程，用于产生10条评论数据*/

--  -- -- -- delete from reviews where operateID>15
--  -- -- -- 
--  -- -- -- delete from contentinfos where ObjectType=5


ALTER         proc  Create100Comments
as  
declare   @i   int
declare   @ContentID   bigint  
set   @i = 1
while   @i <= 10
begin

         BEGIN   TRAN
          if (  @@error   !=   0  )
             goto  ErrorHandler
         commit   tran

     INSERT   INTO   [ ContentInfos ]
        ( CategoryID,Source,
                    ObjectType,ClickCount,
                    ReviewCount,Grade,
                    VoteCount,CommendCount,
                    AuthorID,BlogID,
                    Size,CollectionTime,
                    IsActive,Keyword,
                    CollectionUser,OriginalID,
                    OriginalURL,F1,
                    F2,F3,
                    F4)
     VALUES
        (  15 , '' ,
                     5 , 100 ,
                     100 , 1000 ,
                     100 , 99 ,
                     54 , 204562 ,
                     10000 , getdate (),
                     0 , '' ,
                     ' testuser ' , 999 ,
                     ' testurl ' , 100 ,
                     100 , '' ,
                     '' )

     SET   @ContentID   =  IDENT_CURRENT( ' ContentInfos ' )
     INSERT   INTO   Reviews (CellID ,  PortalID ,  UserID ,  ContentID ,  OperateTitle ,  OperateContent ,  OperateDateTime ,  BlogID ,  BlogTitle ,  OperateUserID ,  UserName ,  UserNick ,  CBlogName ,  OperateEmail ,  OperateIP ,  IsAnonymous ,  IsActive ,  OperateHomePage ,  OperateType ,  OperateValue )
         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 )


            
     if (  @@error   !=   0  )
         goto  ErrorHandler

ErrorHandler:
     if (  @@error   !=   0  )
     begin
         rollback   tran
     end
set   @i = @i + 1

end

GO
SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO

2  产生分页的存储过程 






SET  QUOTED_IDENTIFIER  ON  
GO
SET  ANSI_NULLS  ON  
GO



/**/ /****** Object:  Stored Procedure dbo.CPP_GetTheLastSchoolsWithPage   Script Date: 2006-5-11 10:40:08 ******/


/**/ /* ##SUMMARY 查询一个学校的话题或者活动等 */

--  ##REMARKS Authors :wht    Date:2006-6-5
--  ##PARAM @PageSize     页大小          整型INT
--  ##PARAM @PageIndex    页索引       整型INT
--  ##PARAM @RowCount     总记录数          整型INT



-- CPP_GetSchoolThemeWithPageNew 10,0,100


create   procedure  CPP_Getthesis_thesisWithPageNew
(
    @PageSize         INT ,
    @PageIndex        INT ,
    @RowCount         INT
    -- --@whereClauses varchar(1000)
)

AS

DECLARE   @SQL   VARCHAR ( 5000 )
declare   @PageCount   int
declare   @currentPageSize    int  





-- 计算总页数
SET   @PageCount = CASE   WHEN   @RowCount % @PageSize = 0   THEN   @RowCount / @PageSize   ELSE   @RowCount / @PageSize + 1   END

SET   @PageIndex = @PageIndex + 1

-- 第一页
IF   @PageIndex <= 1  
BEGIN                 
        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 
         FROM  School_Themes 
        
         order by id desc)t inner join school v on t.SchoolID = v.SchoolID order by id desc '

END
ELSE  
BEGIN
     -- 最后一页
     IF   @PageIndex >= @PageCount   OR   @PageIndex <= 0  
         BEGIN
             set   @currentPageSize =   @RowCount - ( @PageCount - 1 ) * @PageSize
                         if ( @currentPageSize <= 0 )
             begin
                             set   @currentPageSize = @PageSize
                         end

             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
                     FROM  School_Themes
                     order by id desc)t inner join school v on t.SchoolID = v.SchoolID order by id desc '

         END
     ELSE
         BEGIN
             -- 中间页（上）
             IF   @PageIndex > 1   AND   @PageIndex <= @PageCount / 2 + 1
                 BEGIN
                     SET   @SQL = '                     
                        SELECT TOP  ' + CONVERT ( VARCHAR ( 15 ), @PageSize ) + '  SchoolName, t.*
                        FROM (
                            SELECT TOP  ' + CONVERT ( VARCHAR ( 15 ), @PageSize ) + '  ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
                            FROM (
                                SELECT TOP  ' + CONVERT ( VARCHAR ( 15 ), @pageSize * @pageIndex ) + '   ID, Title, Context, CreateTime, BlogID, AuthorBlogName, Author, Type, IsTop, ReplyCount, ReplyTime, ReplyContext, ReplyBlogID, ReplyBlogName, ReplyNickName, SchoolID, SchoolRegionname
                                        FROM  School_Themes
                                 
                                 order by id desc              
                                  )AS A                                 
                            order by id asc    
                              )AS t  inner join school v on t.SchoolID = v.SchoolID               
                        order by id desc '
                 END
             ELSE
             -- 中间页（下）
                 BEGIN
                     SET   @SQL = ' SELECT TOP  ' + CONVERT ( VARCHAR ( 15 ), @PageSize ) + '  shoolName, t.*
                          FROM (
                            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
                                    FROM  School_Themes
                             
                             order by id asc    
                               )AS t    inner join school v on t.SchoolID = v.SchoolID                                            
                              order id desc     '  

                 END
         END
END
print   @SQL
EXEC  ( @SQL )




GO
SET  QUOTED_IDENTIFIER  OFF  
GO
SET  ANSI_NULLS  ON  
GO

转载于:https://blog.51cto.com/104660/21008