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