三个很常用的存储过程(downmoon)

两个很常用的存储过程 

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

3、根据存储过程名取存储过程内容

--      根据存储过程名取存储过程内容
--  　调试　GetContentByProcedureName '[dbo].[CPP_CountOfTrdeByUserID]'
alter   procedure  GetContentByProcedureName
( @ProcedureName   nvarchar ( 500 ))
as  

if   exists  ( select   *   from  dbo.syscomments  where  id = object_id (N '' + @ProcedureName + '' )) 
select  c. text , c.encrypted, c. number , xtype = convert ( nchar ( 2 ), o.xtype),    
  datalength (c. text ),  convert ( varbinary ( 8000 ), c. text ),  0   from  dbo.syscomments c, dbo.sysobjects o   
   where  o.id  =  c.id  and  c.id  =   object_id (N '' + @ProcedureName + '' ) 
order   by  c. number , c.colid  option (robust  plan )