Stored Procedure Icon dbo.forums_GetSearchResultsByUser

Stored Procedure Properties

Name Value
Owner dbo
Created 2004-05-31
Startup False
Encrypted False

Creation Options

Name Value
QUOTED_IDENTIFIER OFF
ANSI_NULLS OFF

Parameters

Name DataType Length Type
@Page int 4 INPUT
@RecsPerPage int 4 INPUT
@ForumID int 4 INPUT
@UserPattern nvarchar 100 INPUT
@UserName nvarchar 100 INPUT
@MoreRecords bit 1 OUTPUT

Total: 6 parameter(s)

SQL

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO



CREATE PROCEDURE forums_GetSearchResultsByUser (
    @Page int,
    @RecsPerPage int,
    @ForumID int = 0,
    @UserPattern nvarchar(50),
    @UserName nvarchar(50) = NULL,
    @MoreRecords bit output
) AS
    -- Performance optimizations
    SET NOCOUNT ON
    -- Global declarations
    DECLARE @sql nvarchar(1000)
    DECLARE @FirstRec int, @LastRec int, @MoreRec int

    SET @FirstRec = (@Page - 1) * @RecsPerPage;
    SET @LastRec = (@FirstRec + @RecsPerPage);
    SET @MoreRec = @LastRec + 1;
    SET @MoreRecords = 0;

    CREATE TABLE #SearchResults (
        IndexID int IDENTITY(1,1),
        PostID int
    )

    -- Turn on rowcounting for performance
    SET ROWCOUNT @MoreRec;
    INSERT INTO #SearchResults(PostID)
    SELECT PostID
    FROM Posts P (nolock)
    WHERE
        Approved = 1 AND
        (
            @ForumID = 0 OR
            ForumID = @ForumID
        ) 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))
        ) AND
        0 < ISNULL(PATINDEX(@UserPattern, Username), 1)
    ORDER BY ThreadDate DESC
    IF @@ROWCOUNT > @LastRec SET @MoreRecords = 1
    SET ROWCOUNT 0
    -- Turn off rowcounting

    -- Select the data out of the temporary table
    SELECT
        P.*,
        HasRead = 0 -- not used
    FROM 
        forums_Post P (nolock), #SearchResults
    WHERE
        forums_Post.PostID = #SearchResults.PostID AND
        #SearchResults.IndexID > @FirstRec AND
        #SearchResults.IndexID <= @LastRec
    ORDER BY #SearchResults.IndexID ASC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


					

Generated on 26/08/2004 15:05:30 by DataAide.