A Stored Procedure from Telligent

  • 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
    SET Transaction Isolation Level Read UNCOMMITTED

     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
      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,
      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
      cs_Posts P,
      cs_vw_Users_FullUser U,
      cs_Threads T,
      #SearchResults R
      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

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

Log in to reply

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.