sqlserver存储过程创建

发布时间：2020-12-12 14:57:04 所属栏目：MsSql教程来源：网络整理

导读：USE [master]?? ??? ??? ??? ? GO?? ??? ??? ??? ? SET ANSI_NULLS ON?? ??? ??? ??? ? GO?? ??? ??? ??? ? SET QUOTED_IDENTIFIER ON?? ??? ??? ??? ? GO?? ??? ??? ??? ? -- =============================================?? ??? ??? ??? ? -- Author: **

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!