cs_thread_rate.prc

解压即可使用

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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




create procedure [dbo].cs_Thread_Rate
(
	@ThreadID int,
	@UserID int,
	@Rating int,
	@SettingsID int
)
AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN

	IF EXISTS (SELECT ThreadID FROM cs_PostRating WHERE UserID = @UserID AND ThreadID = @ThreadID)
	BEGIN
		-- Get the User's Current Rating
		DECLARE @CurrentRating int
		SET @CurrentRating = (SELECT Rating FROM cs_PostRating WHERE UserID = @UserID AND ThreadID = @ThreadID and SettingsID = @SettingsID)

		IF @CurrentRating = @Rating
			RETURN

		-- Update the rating
		UPDATE
			cs_PostRating
		SET 
			Rating = @Rating
		WHERE
			UserID = @UserID AND
			ThreadID = @ThreadID and SettingsID = @SettingsID

		-- Update the thread rating
		UPDATE
			cs_Threads
		SET
			RatingSum = (RatingSum - @CurrentRating) + @Rating
		WHERE
			ThreadID = @ThreadID and SettingsID = @SettingsID

	END
	ELSE
	BEGIN
		-- Add an entry into the post rating table
		INSERT INTO
			cs_PostRating
		VALUES
			(@UserID, @ThreadID, @Rating, @SettingsID)

		-- Update the thread rating
		UPDATE
			cs_Threads
		SET
			RatingSum = RatingSum + @Rating,
			TotalRatings = TotalRatings + 1
		WHERE
			ThreadID = @ThreadID and SettingsID = @SettingsID
	END

END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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