A Stored Procedure from Telligent

Savior

The search stored proc from our beloved forum:

CREATE procedure [dbo].cs_forums_Search (
@SearchSQL nvarchar(4000),
--@RecordCountSQL nvarchar(4000),
@PageIndex int = 0,
@PageSize int = 25,
@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

DECLARE @StartTime datetime
DECLARE @RowsToReturn int
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @Count int

-- Used to calculate cost of query
SET @StartTime = GetDate()

-- Set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
-- SET ROWCOUNT @RowsToReturn

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

-- Create a temp table to store the results in
CREATE TABLE #SearchResults
(
  IndexID int IDENTITY (1, 1) NOT NULL,
  PostID int,
  SectionID int,
  Weight int,
  PostDate datetime
)

Declare @TotalResults int

-- Fill the temp table
INSERT INTO #SearchResults (PostID, SectionID, Weight, PostDate)
exec (@SearchSQL)

Set @TotalResults = @@rowcount

-- SELECT actual search results from this table
SELECT
  P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.PostLevel, P.SortOrder, P.Subject, P.PostDate, P.IsApproved,
  P.IsLocked, P.IsIndexed, P.TotalViews, P.Body, P.FormattedBody, P.IPAddress, P.PostType, P.EmoticonID, P.SettingsID, P.AggViews,
  P.PropertyNames as PostPropertyNames, P.PropertyValues as PostPropertyValues,
  P.PostConfiguration,
  U.*,
  T.ThreadDate,
  T.IsLocked,
  T.IsSticky,
  AttachmentFilename = '', --ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
  Replies = 0, -- (SELECT COUNT(P2.PostID) FROM cs_Posts P2 (nolock) WHERE P2.ParentID = P.PostID AND P2.PostLevel != 1),
  IsModerator = 0, --(SELECT count(*) from cs_Moderators where UserID = P.UserID),
  HasRead = 0 -- not used
FROM
  cs_Posts P,
  cs_vw_Users_FullUser U,
  cs_Threads T,
  #SearchResults R
WHERE
  P.PostID = R.PostID AND
  T.ThreadID = P.ThreadID AND
  U.cs_UserID = P.UserID AND
  R.IndexID > @PageLowerBound AND
  R.IndexID < @PageUpperBound AND
  P.SettingsID = @SettingsID

-- Do we need to return a record estimate?
--exec (@RecordCountSQL)

Select @TotalResults

--DROP Table #SearchResults

SELECT Duration = GetDate() - @StartTime
END

Joost_

Beautiful! I wonder if it's thread-safe. If not, I propose they give the table SearchResults a random name instead.