两个很常用的存储过程
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)