概述USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ==================================== USE [master] GO SET ANSI_NulLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: ** -- Create date: 2011-06-22 -- Description: 添加中奖记录 -- 成功返回失败返回 -- ============================================= CREATE PROCEDURE prMgame_addPrizesRecord @intUserID int,--用户ID @vchIptvname varchar(50),--IPTV名称 @intPrizesType smallint,--周奖还是终极大奖 0:周奖 1:终极大奖 @intPrizesNo int,--奖品编号 @vchPrizesname varchar(50)=null output,--奖品名称 @outPrizesNo int=0 output,--奖品编号 @intReturnCode int=1 output,--处理返回值 @vchMsg varchar(128)=null output --处理信息 AS BEGIN BEGIN TRANSACTION DECLARE @NowTime DATETIME DECLARE @NowPrizesSum INT DECLARE @NowRank INT DECLARE @NowWeek smallint --当天属于哪个星期 DECLARE @lestWeek smallint SELECT @NowTime=GETDATE() FROM dbo.sysobjects --获取系统时间 --取得第几周 IF NOT EXISTS (SELECT * FROM WeekTime WHERE WeekStartTime<=@NowTime AND WeekEndTime >=@NowTime) BEGIN SET @intReturnCode=1 SET @vchMsg='奖品添加失败,无法获取周号。' RolLBACK TRANSACTION RETURN 1 END SELECT @NowWeek=WeekNo FROM WeekTime WHERE WeekStartTime<=@NowTime AND WeekEndTime >=@NowTime SET @lestWeek = @NowWeek - 1 print @lestWeek IF (@intPrizesType = 0) BEGIN --周奖 IF EXISTS (SELECT * FROM PrizesRecode WHERE UserID=@intUserID AND WeekNo=@lestWeek AND PrizesType=@intPrizesType) BEGIN SET @intReturnCode=1 SET @vchMsg='奖品添加失败,用户已经抽过奖了。' RolLBACK TRANSACTION RETURN 1 END IF EXISTS (SELECT * FROM Prizes WHERE PrizesNo=@intPrizesNo AND PrizesRemainder > 0) BEGIN --奖品数量充足 SELECT @NowPrizesSum=PrizesRemainder,@vchPrizesname=Prizesname,@outPrizesNo=PrizesNo FROM Prizes WHERE PrizesNo=@intPrizesNo SET @NowPrizesSum = @NowPrizesSum - 1 UPDATE Prizes SET PrizesRemainder=@NowPrizesSum WHERE PrizesNo=@outPrizesNo IF @@ERROR<>0 BEGIN SET @intReturnCode=1 SET @vchMsg='奖品添加失败,奖品表更新失败。' RolLBACK TRANSACTION RETURN 1 END END ELSE BEGIN --奖品数量不足 SELECT top 1 @NowPrizesSum=PrizesRemainder,@outPrizesNo=PrizesNo FROM Prizes WHERE PrizesRemainder > 0 AND PrizesType = @intPrizesType ORDER BY PrizesRemainder DESC SET @NowPrizesSum = @NowPrizesSum - 1 UPDATE Prizes SET PrizesRemainder=@NowPrizesSum WHERE PrizesNo=@outPrizesNo IF @@ERROR<>0 BEGIN SET @intReturnCode=1 SET @vchMsg='奖品添加失败,奖品表更新失败。' RolLBACK TRANSACTION RETURN 1 END END SELECT @NowRank=WeekRank FROM WeekRank WHERE UserID=@intUserID AND WeekNo=@lestWeek INSERT INTO PrizesRecode (UserID,Iptvname,PrizesNo,PrizesType,RecodeTime,WeekNo,GameRank) VALUES (@intUserID,@vchIptvname,@outPrizesNo,@intPrizesType,@NowTime,@lestWeek,@NowRank) IF @@ERROR<>0 BEGIN SET @intReturnCode=1 SET @vchMsg='奖品添加失败,奖品记录表更新失败。' RolLBACK TRANSACTION RETURN 1 END SET @intReturnCode=0 SET @vchMsg='奖品获取成功' COMMIT TRANSACTION RETURN 0 END ELSE IF (@intPrizesType = 1) BEGIN --终极大奖 IF EXISTS (SELECT * FROM PrizesRecode WHERE UserID=@intUserID AND PrizesType=@intPrizesType) BEGIN SET @intReturnCode=1 SET @vchMsg='奖品添加失败,用户已经抽过奖了。' RolLBACK TRANSACTION RETURN 1 END IF EXISTS (SELECT * FROM Prizes WHERE PrizesNo=@intPrizesNo AND PrizesRemainder > 0) BEGIN --奖品数量充足 SELECT @NowPrizesSum=PrizesRemainder,@outPrizesNo=PrizesNo FROM Prizes WHERE PrizesRemainder > 0 AND PrizesType = @intPrizesType ORDER BY PrizesRemainder DESC SET @NowPrizesSum = @NowPrizesSum - 1 UPDATE Prizes SET PrizesRemainder=@NowPrizesSum WHERE PrizesNo=@outPrizesNo IF @@ERROR<>0 BEGIN SET @intReturnCode=1 SET @vchMsg='奖品添加失败,奖品表更新失败。' RolLBACK TRANSACTION RETURN 1 END END SELECT @NowRank=AllRank FROM AllRank WHERE UserID=@intUserID INSERT INTO PrizesRecode (UserID,@NowRank) IF @@ERROR<>0 BEGIN SET @intReturnCode=1 SET @vchMsg='奖品添加失败,奖品记录表更新失败。' RolLBACK TRANSACTION RETURN 1 END SET @intReturnCode=0 SET @vchMsg='奖品获取成功' COMMIT TRANSACTION RETURN 0 END SET @intReturnCode=1 SET @vchMsg='奖品添加失败,参数传入错误。' RolLBACK TRANSACTION RETURN 1 END 总结
以上是内存溢出为你收集整理的sqlserver存储过程创建全部内容,希望文章能够帮你解决sqlserver存储过程创建所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
评论列表(0条)