cs_referrals_get.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_referrals_Get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_referrals_Get]
GO

CREATE Proc [dbo].cs_referrals_Get
(
	@SettingsID int,
	@SectionID int,
	@PostID int,
	@PageSize int,
	@PageIndex int,
	@TotalRecords int output
)
AS
SET Transaction Isolation Level Read UNCOMMITTED


-- are we getting referrals for a single post
IF (@PostID <> -1)
BEGIN

	-- return all referrals (no paging)
	SELECT
		P.Subject,
		R.ReferralID,
		R.SettingsID,
		R.SectionID,
		P.PostID,
		U.Url,
		R.Hits,
		R.LastDate
	FROM
		cs_Referrals R
		INNER JOIN cs_Urls U ON R.UrlID = U.UrlID
		INNER JOIN cs_Posts P ON R.PostID = P.PostID
	WHERE
		R.SettingsID = @SettingsID
		AND R.SectionID = @SectionID
		AND R.PostID = @PostID
	ORDER BY
		R.LastDate DESC	

	-- return record count
	SELECT
		@TotalRecords = COUNT(*)
	FROM
		cs_Referrals R
	WHERE
		R.SettingsID = @SettingsID
		AND R.SectionID = @SectionID
		AND R.PostID = @PostID

	-- exit immediately
	RETURN
END


-- we are getting referrals for multiple posts (use paging)
DECLARE @RowsToReturn int
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

CREATE TABLE #PageIndex 
(
	IndexID int IDENTITY (1, 1) NOT NULL,
	ReferralID int
)

Insert #PageIndex (ReferralID)
Select ReferralID FROM cs_Referrals where SettingsID = @SettingsID and  SectionID = @SectionID order by LastDate desc

-- Reset rowcount and get count of total records
SET ROWCOUNT 0
Select @TotalRecords = count(*) From cs_Referrals Where SettingsID = @SettingsID and SectionID = @SectionID

Select  cs_Posts.Subject,  cs_Referrals.ReferralID, cs_Referrals.SettingsID,  cs_Referrals.SectionID, cs_Posts.PostID, Url, Hits, LastDate
FROM cs_Referrals, cs_Urls, #PageIndex, cs_Posts
Where
	cs_Referrals.ReferralID = #PageIndex.ReferralID and
	cs_Urls.UrlID = cs_Referrals.UrlID and 
	#PageIndex.IndexID > @PageLowerBound AND
	#PageIndex.IndexID < @PageUpperBound AND 
	cs_Posts.PostID = cs_Referrals.PostID
Order by IndexID

DROP Table #PageIndex


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].[cs_referrals_Get] to public
go