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

两个很常用的存储过程

1 用于产生10条评论数据

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

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

-- ------deletefromreviewswhereoperateID>15
-- ------
-- ------deletefromcontentinfoswhereObjectType=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名称 ' , ' [e-mail] ' , ' 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:StoredProceduredbo.CPP_GetTheLastSchoolsWithPageScriptDate:2006-5-1110:40:08***** */


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

-- ##REMARKSAuthors:whtDate:2006-6-5
-- ##PARAM@PageSize页大小整型INT
-- ##PARAM@PageIndex页索引整型INT
-- ##PARAM@RowCount总记录数整型INT



-- CPP_GetSchoolThemeWithPageNew10,0,100


create procedure CPP_Getthesis_thesisWithPageNew
(
@PageSize INT ,
@PageIndex INT ,
@RowCount INT
-- --@whereClausesvarchar(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 = ' selectSchoolName,t.*from(SELECTtop ' + cast ( @PageSize as varchar ( 10 )) + ' ID,Title,Context,CreateTime,BlogID,AuthorBlogName,Author,Type,IsTop,ReplyCount,ReplyTime,ReplyContext,ReplyBlogID,ReplyBlogName,ReplyNickName,SchoolID,SchoolRegionname
FROMSchool_Themes

orderbyiddesc)tinnerjoinschoolvont.SchoolID=v.SchoolIDorderbyiddesc '

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 = ' selectSchoolName,t.*
from(SELECTtop ' + cast ( @PageSize as varchar ( 10 )) + ' ID,Title,Context,CreateTime,BlogID,AuthorBlogName,Author,Type,IsTop,ReplyCount,ReplyTime,ReplyContext,ReplyBlogID,ReplyBlogName,ReplyNickName,SchoolID,SchoolRegionname
FROMSchool_Themes
orderbyiddesc)tinnerjoinschoolvont.SchoolID=v.SchoolIDorderbyiddesc '

END
ELSE
BEGIN
-- 中间页（上）
IF @PageIndex > 1 AND @PageIndex <= @PageCount / 2 + 1
BEGIN
SET @SQL = '
SELECTTOP ' + CONVERT ( VARCHAR ( 15 ), @PageSize ) + ' SchoolName,t.*
FROM(
SELECTTOP ' + CONVERT ( VARCHAR ( 15 ), @PageSize ) + ' ID,Title,Context,CreateTime,BlogID,AuthorBlogName,Author,Type,IsTop,ReplyCount,ReplyTime,ReplyContext,ReplyBlogID,ReplyBlogName,ReplyNickName,SchoolID,SchoolRegionname
FROM(
SELECTTOP ' + CONVERT ( VARCHAR ( 15 ), @pageSize * @pageIndex ) + ' ID,Title,Context,CreateTime,BlogID,AuthorBlogName,Author,Type,IsTop,ReplyCount,ReplyTime,ReplyContext,ReplyBlogID,ReplyBlogName,ReplyNickName,SchoolID,SchoolRegionname
FROMSchool_Themes

orderbyiddesc
)ASA
orderbyidasc
)AStinnerjoinschoolvont.SchoolID=v.SchoolID
orderbyiddesc '
END
ELSE
-- 中间页（下）
BEGIN
SET @SQL = ' SELECTTOP ' + CONVERT ( VARCHAR ( 15 ), @PageSize ) + ' shoolName,t.*
FROM(
SELECTTOP ' + CONVERT ( VARCHAR ( 15 ), @rowCount - @pageSize * @pageIndex + @pageSize ) + ' ID,Title,Context,CreateTime,BlogID,AuthorBlogName,Author,Type,IsTop,ReplyCount,ReplyTime,ReplyContext,ReplyBlogID,ReplyBlogName,ReplyNickName,SchoolID,SchoolRegionname
FROMSchool_Themes

orderbyidasc
)AStinnerjoinschoolvont.SchoolID=v.SchoolID
orderiddesc '

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.syscommentsc,dbo.sysobjectso
where o.id = c.id and c.id = object_id (N '' + @ProcedureName + '' )
order by c. number ,c.colid option (robust plan )