两个常用的存储过程(转)

 

两个很常用的存储过程  
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名称', 'test@126.com', '124.458.135.500', 0, 1, 'http://www.blogcn.com', 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