cs_searchbarrel_search.prc

community server 源码

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_SearchBarrel_Search]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_SearchBarrel_Search]
GO





CREATE procedure dbo.cs_SearchBarrel_Search (
	@SearchSQL NText,
	--@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
	DECLARE @TotalRecords 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
	)

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

	SET @TotalRecords = @@rowcount

	SET ROWCOUNT @RowsToReturn

	-- SELECT actual search results from this table
	SELECT
		P.PostID, P.ThreadID, P.ParentID, P.PostAuthor, P.UserID, P.SectionID, P.Subject, P.PostDate,
		P.FormattedBody, P.IPAddress, P.PostType, P.SettingsID, P.UserTime,
		P.ApplicationPostType, P.PostName, P.UserTime,
		AttachmentFilename =  ISNULL ( (SELECT [FileName] FROM cs_PostAttachments WHERE PostID = P.PostID), ''),
		S.ApplicationKey, S.GroupID, S.ApplicationType, S.Name as SectionName

	FROM 
		cs_Posts P,
		cs_Sections S,
		#SearchResults R
	WHERE
		P.PostID = R.PostID AND
		P.SectionID = S.SectionID AND
		R.IndexID > @PageLowerBound AND
		R.IndexID < @PageUpperBound AND
		P.SettingsID = @SettingsID
	Order By IndexID

	DROP Table #SearchResults

	Select @TotalRecords

	SELECT Duration = GetDate() - @StartTime
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[cs_SearchBarrel_Search]  TO [public]
GO