cs_referrals_get.prc

解压即可使用

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


if @PostID <> -1
Begin
	Insert #PageIndex (ReferralID)
	Select ReferralID FROM cs_Referrals where SettingsID = @SettingsID and SectionID = @SectionID and PostID = @PostID 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 and PostID = @PostID

End
Else
Begin
	Insert #PageIndex (ReferralID)
	Select ReferralID FROM cs_Referrals where SettingsID = @SettingsID and  SectionID = @SectionID order by ReferralID

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

End

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

--Select @TotalRecords = Count(*) FROM #PageIndex

DROP Table #PageIndex

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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