三个很常用的存储过程-原创手记-慕课网

两个很常用的存储过程

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 评论内容2 评论内容1 评论内容2 评论内容2 评论内容4 ', 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)