学习Asp.Net Forum 论坛存储过程中的一些摘抄

抽了半天时间把Asp.Net Forum 论坛V2.0(中文版本)的180多个存储过程一个个看了一下

有些东西写的确实不错

平时自己写的时候有些并不是这样做的,摘抄出来,以后可以学习一下

/**//*
用一个存储过程实现一个表的增删改
*/
CREATE proc forums_Censorship_CreateUpdateDelete
(
      @Word            nvarchar(40)
    , @DeleteWord     bit = 0
    , @Replacement    nvarchar(40)
)
as
SET NOCOUNT ON

if( @DeleteWord > 0 )
BEGIN
    DELETE FROM
        forums_Censorship
    WHERE
        Word = @Word
    RETURN
END
ELSE
BEGIN
    UPDATE forums_Censorship SET
        Replacement    = @Replacement
    WHERE
        Word    = @Word

    IF( @@rowcount = 0 )
    BEGIN
    INSERT INTO forums_Censorship (
        Word, Replacement
    ) VALUES (
        @Word, @Replacement
    )
    END
END


/**//*
直接使用EXISTS和 NOT EXISTS进行条件判断
*/
CREATE PROCEDURE forums_AddForumToRole
(
   @ForumID int,
   @Rolename nvarchar(256)
)
AS
IF NOT EXISTS (SELECT ForumID FROM PrivateForums WHERE ForumID=@ForumID AND Rolename=@Rolename) AND
    EXISTS (SELECT ForumID FROM Forums WHERE ForumID=@ForumID) AND
    EXISTS (SELECT Rolename FROM UserRoles WHERE Rolename=@Rolename)
    BEGIN
        INSERT INTO
            PrivateForums(ForumID, RoleName)
        VALUES
            (@ForumID, @Rolename)
    END


/**//*
coalesce(max(SortOrder) + 1, 1)
coalesce 返回当中第一个不为NULL的值
如果max(SortOrder)为NULL,则+1后还是NULL
*/
if( @SortOrder = 0 ) 
begin
    select @SortOrder = coalesce(max(SortOrder) + 1, 1) from forums_Forums where ForumGroupID = @ForumGroupID
end

SELECT @replaceSortValue = coalesce(f.SortOrder, -1), @replaceForumID = coalesce(f.ForumID, -1)
    FROM forums_Forums f
        inner join (
            select top 1 * 
            FROM forums_Forums 
            WHERE ForumGroupID = @ForumGroupID and SortOrder > @currentSortValue order by SortOrder ASC                
        ) as pf on 
            pf.ForumID = f.ForumID


if( @replaceSortValue != -1 )
begin        
    UPDATE forums_Forums SET SortOrder = @currentSortValue WHERE ForumID = @replaceForumID
    UPDATE forums_Forums SET SortOrder = @replaceSortValue WHERE ForumID = @ForumID
end


/**//*
注意ELSE IF的用法
*/
IF @Action = 0
ELSE IF @Action = 1
ELSE IF @Action = 2


/**//*
比较复杂的逻辑
*/
SELECT
    F.*,
    LastUserActivity = (CASE @UserID
        WHEN 0 THEN '1/1/1797'
        ELSE ( ISNULL( (SELECT LastActivity FROM forums_ForumsRead WHERE UserID = @UserID AND ForumID = F.ForumID), '1/1/1797'))
        END)
FROM
    forums_Forums F
WHERE
    (SiteID = @SiteID OR SiteID = 0) AND
    IsActive = 1


/**//*
注意UNION的用法
*/
SELECT ForumID =  0, ForumName =  'All Forums'
UNION
SELECT
    ForumID,
    ForumName = F.Name
FROM Forums F (nolock) 
WHERE ForumID NOT IN (SELECT ForumID FROM Moderators (nolock) WHERE Username = @UserName)

/**//*
不用INNER JOIN
*/
SELECT
    Email
FROM 
    Users U (nolock),
    ForumTrackings F
WHERE
    U.Username = F.Username AND
    F.ForumID = @ForumID AND
    F.subType = @SubscriptionType


/**//*
临时表的例子
*/
CREATE     PROCEDURE forums_GetSearchResults
(
    @SearchTerms    nvarchar(500),
    @Page int,
    @RecsPerPage int,
    @UserName nvarchar(50)
)
 AS
    CREATE TABLE #tmp
    (
        ID int IDENTITY,
        PostID int
    )
    DECLARE @sql nvarchar(1000)
    SET NOCOUNT ON
    SELECT @sql = 'INSERT INTO #tmp(PostID) SELECT PostID ' + 
            'FROM Posts P (nolock) INNER JOIN Forums F (nolock) ON F.ForumID = P.ForumID ' +
            @SearchTerms + ' ORDER BY ThreadDate DESC'
    EXEC(@sql)

    -- ok, all of the rows are inserted into the table.
    -- now, select the correct subset
    DECLARE @FirstRec int, @LastRec int
    SELECT @FirstRec = (@Page - 1) * @RecsPerPage
    SELECT @LastRec = (@Page * @RecsPerPage + 1)
    DECLARE @MoreRecords int
    SELECT @MoreRecords = COUNT(*)  FROM #tmp -- WHERE ID >= @LastRec


    -- Select the data out of the temporary table
    IF @UserName IS NOT NULL
        SELECT
            T.PostID,
            P.ParentID,
            P.ThreadID,
            P.PostLevel,
            P.SortOrder,
            P.UserName,
            P.Subject,
            P.PostDate,
            P.ThreadDate,
            P.Approved,
            P.ForumID,
            F.Name As ForumName,
            MoreRecords = @MoreRecords,
            Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
            P.Body,
            P.TotalViews,
            P.IsLocked,
            HasRead = 0 -- not used
        FROM 
            #tmp T
            INNER JOIN Posts P (nolock) ON
                P.PostID = T.PostID
            INNER JOIN Forums F (nolock) ON
                F.ForumID = P.ForumID
        WHERE 
            T.ID > @FirstRec AND ID < @LastRec AND
            (P.ForumID NOT IN (SELECT ForumID from PrivateForums) OR
            P.ForumID IN (SELECT ForumID FROM PrivateForums WHERE RoleName IN (SELECT RoleName from UsersInRoles WHERE username = @UserName)))
    ELSE
        SELECT
            T.PostID,
            P.ParentID,
            P.ThreadID,
            P.PostLevel,
            P.SortOrder,
            P.UserName,
            P.Subject,
            P.PostDate,
            P.ThreadDate,
            P.Approved,
            P.ForumID,
            F.Name As ForumName,
            MoreRecords = @MoreRecords,
            Replies = (SELECT COUNT(*) FROM Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
            P.Body,
            P.TotalViews,
            P.IsLocked,
            HasRead = 0 -- not used
        FROM 
            #tmp T
            INNER JOIN Posts P (nolock) ON
                P.PostID = T.PostID
            INNER JOIN Forums F (nolock) ON
                F.ForumID = P.ForumID
        WHERE 
            T.ID > @FirstRec AND ID < @LastRec AND
            P.ForumID NOT IN (SELECT ForumID from PrivateForums)

    SET NOCOUNT OFF


/**//*
CONTAINS的用法
下面的示例查找包含词“bottles”且价格为 $15.00 的所有产品。

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE UnitPrice = 15.00
   AND CONTAINS(QuantityPerUnit, 'bottles')
GO

*/



/**//*
比较复杂的
*/

CREATE PROCEDURE forums_GetUsersByName
(
    @PageIndex int,
    @PageSize int,
    @UserNameToFind nvarchar(50)
)
AS

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

-- Create a temp table to store the select results
CREATE TABLE #PageIndexForUsers 
(
    IndexID int IDENTITY (1, 1) NOT NULL,
    UserID int
)    

-- Insert into our temp table
INSERT INTO #PageIndexForUsers (UserID)
SELECT 
    U.UserID 
FROM 
    forums_Users U,
    forums_UserProfile P
WHERE 
    U.UserID = P.UserID AND
    UserAccountStatus = 1 AND 
    EnableDisplayInMemberList = 1 AND
    UserName like '%' + @UserNameToFind + '%'
ORDER BY 
    DateCreated


SELECT
    *,
    IsModerator = (select count(*) from moderators where username = U.UserName)
FROM 
    forums_Users U (nolock),
    forums_UserProfile P,
    #PageIndexForUsers
WHERE 
    U.UserID = #PageIndexForUsers.UserID AND
    #PageIndexForUsers.IndexID > @PageLowerBound AND
    #PageIndexForUsers.IndexID < @PageUpperBound AND
    U.UserID = P.UserID AND
    UserAccountStatus = 1

ORDER BY
    #PageIndexForUsers.IndexID



/**//*
注意Having的用法
*/
SELECT 
    Vote, 
    count(*) as VoteCount 
FROM 
    forums_Vote 
GROUP BY Vote, PostID 
HAVING 
    PostID = @PostID 


/**//*
异常处理的一些方法
*/
-- Are we updating a forum
IF  @RoleID > 0
BEGIN
    -- Update the role
    UPDATE 
        forums_Roles
    SET
        Name = @Name,
        Description = @Description
    WHERE 
        RoleID = @RoleID

    if( @@error != 0 )
        goto ErrorHandler

    select 1
    return
END
ELSE
BEGIN

    if exists( select * from forums_Roles where [Name] = @Name )
    begin
        select 2
        return
    end

    -- Create a new Forum
    INSERT INTO 
        forums_Roles (
            Name, 
            Description
            )
        VALUES (
            @Name,
            @Description
            )
    
    if( @@error != 0 )
        goto ErrorHandler

    SET @RoleID = @@Identity

    select 1
    return

ErrorHandler:
    if( @@error != 0 )
    begin
        rollback tran
        select -1
        return
    end

END


/**//*
位操作
*/
CREATE procedure forums_system_ACE_Get
(
    @ForumID int,
    @UserID int,
    @ACE binary(4) out
)
AS
BEGIN
    SET @ACE = 0x00000000
    SELECT @ACE = convert(int, @ACE) ^ ACE FROM forums_ForumPermissions P, forums_UserRoles R WHERE P.RoleID = R.RoleID AND R.UserID = @UserID AND P.ForumID = @ForumID
END


/**//*
IDENTITY_INSERT
插入的值会自动填充到因为删除造成的ID间隙中
*/
SET IDENTITY_INSERT forums_ForumGroups ON
INSERT INTO
    forums_ForumGroups
    (
        ForumGroupID,
        Name,
        SortOrder
    )
VALUES
    (
        @ForumGroupID,
        @Name,
        @SortOrder
    )
SET IDENTITY_INSERT forums_ForumGroups OFF


/**//*
游标的用法
*/
DECLARE @PA_PostID INT
DECLARE @PA_PostCount INT
DECLARE @PA_PostAuthor VARCHAR(255)
DECLARE @PA_UserID INT

DECLARE @Rows1 INT
DECLARE @FS1 INT

SET @Rows1=0
SET @Rows1 = (SELECT COUNT(PostID) FROM forums_Posts)

SET @FS1=0
/**//* ***************************************************************** */
/**//* Get all Posts and PostAuthors */
/**//* ***************************************************************** */
DECLARE PostAuthor_Cursor CURSOR FOR
SELECT PostID, PostAuthor FROM forums_Posts

OPEN PostAuthor_Cursor
FETCH NEXT FROM PostAuthor_Cursor INTO @PA_PostID, @PA_PostAuthor
SET @FS1=@@FETCH_STATUS
 --PRINT @Rows1
WHILE @FS1=0
BEGIN
    --PRINT @FS1
--PRINT 'PostAuthor_Cursor'
    IF(@Rows1>0)
    BEGIN
        IF(@PA_PostAuthor <> '')
        BEGIN
            /**//* **************************************************************************** */
            /**//* Get PostAuthor ID's from users table */
            /**//* **************************************************************************** */
    
            DECLARE PA_UserID_Cursor CURSOR FOR
            Select UserID from forums_Users where UserName = @PA_PostAuthor
    
            OPEN PA_UserID_Cursor
            FETCH NEXT FROM PA_UserID_Cursor INTO @PA_UserID
            PRINT '@PA_UserID:' + CONVERT(CHAR(10), @PA_UserID)
            CLOSE PA_UserID_Cursor
            DEALLOCATE PA_UserID_Cursor
        
            IF @PA_UserID is not NULL and @PA_UserID <> 0
                BEGIN
                    Update forums_posts
                    Set UserID = @PA_UserID
                    Where PostID = @PA_PostID
                    
                    /**//* **************************************************************************** */
                    /**//* Now update number of posts for each user */
                    /**//* **************************************************************************** */
                    
                    DECLARE PA_PostCount_Cursor CURSOR FOR
                    Select Count(PostID) as TotalPosts from forums_Posts where UserID = @PA_UserID
            
                    OPEN PA_PostCount_Cursor
                    FETCH NEXT FROM PA_PostCount_Cursor INTO @PA_PostCount
                    PRINT '@PA_PostCount:' + CONVERT(CHAR(10), @PA_PostCount)

                    --Now Update the stats
                    Update forums_userprofile
                    Set TotalPosts = @PA_PostCount
                    Where UserID = @PA_UserID

                    CLOSE PA_PostCount_Cursor
                    DEALLOCATE PA_PostCount_Cursor



                END
    
            --SET @PA_UserID=0
            FETCH NEXT FROM PostAuthor_Cursor INTO @PA_PostID, @PA_PostAuthor
            SET @FS1=@@FETCH_STATUS
    
        END
    END
END
CLOSE PostAuthor_Cursor
DEALLOCATE PostAuthor_Cursor


/**//*
一个简单的游标用法
*/

DECLARE ForumCount_Cursor CURSOR FOR
    SELECT ForumID FROM forums_Forums

OPEN ForumCount_Cursor
FETCH NEXT FROM ForumCount_Cursor INTO @ForumID

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC forums_system_ResetForumStatistics @ForumID
    FETCH NEXT FROM ForumCount_Cursor INTO @ForumID
END

CLOSE ForumCount_Cursor
DEALLOCATE ForumCount_Cursor

/**//*
SET NOCOUNT { ON | OFF } 

使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信
*/