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
    )
    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



  • 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.