SQLServer存储过程

概述ALTER PROCEDURE [dbo].[h5_newyear_app_draw](@action varchar(100),@game_id int=2,@lot_type varchar(12),@weixin_id varchar(32),@seq varchar(32)='',@source varchar(50)='')ASBEGIN SET

ALTER PROCEDURE [dbo].[h5_newyear_app_draw](@action varchar(100),@game_ID int=2,@lot_type varchar(12),@weixin_ID varchar(32),@seq varchar(32)='',@source varchar(50)='')ASBEGIN  SET NOCOUNT ON; 	  DECLARE @success varchar(32);	DECLARE @code nvarchar(32);	DECLARE @msg nvarchar(128);  DECLARE @rnd int;  DECLARE @lot_ID int;  DECLARE @lot_code nvarchar(32);	--默认错误信息	SET @code='9997'	SET @msg='CODE_ParaMETER_INVALID'	SET @success='false'  IF (@action='API.h5.game.draw')		GOTO action_h5_game_draw;	ELSE		GOTO action_erroraction_h5_game_draw:	PRINT 'action_reg:' + @action	--检查player_code是否已经注册  IF NOT EXISTS (SELECT 1 FROM h5_game_player WHERE player_code=@weixin_ID)     --以前没有抽过奖的直接插入数据,play_ID是对应签的类型1到8		BEGIN			INSERT INTO h5_game_player (game_ID,player_code,play_num,played_num,created,seq,status,player_ID)VALUES(@game_ID,@weixin_ID,2,1,getdate(),@seq,@lot_type);      --防止序列重复      IF NOT EXISTS (SELECT 1 FROM h5_game_play_log WHERE seq=@seq)         INSERT INTO h5_game_play_log (game_ID,from_code) VALUES(@game_ID,GETDATE(),@source);      --去抽奖       GOTO action_draw;		END  ELSE    BEGIN      IF (SELECT count(0) FROM h5_game_player WHERE player_code=@weixin_ID AND points=1)>0          BEGIN               SET @code = '8889';              SET @msg = '您已经中过奖了,不能继续参与抽奖';              GOTO action_msg_end;          END      IF (SELECT count(0) FROM h5_game_play_log WHERE player_code=@weixin_ID AND game_ID=@game_ID)>=2          BEGIN               SET @code = '9000';              SET @msg = '您已经抽取两次了';              GOTO action_msg_end;          END      IF EXISTS (SELECT 1 FROM h5_game_play_log WHERE player_code=@weixin_ID AND game_ID=@game_ID AND from_code=@source)          BEGIN               SET @code = '9001';              IF (@source='pulicNo')                 SET @msg = '已经在公众号中抽过一次奖，请到非公众号里进行抽奖';              ELSE								 SET @msg = '已经在非公众号中抽过一次奖，请到公众号里进行抽奖';									               GOTO action_msg_end;          END      --插入抽奖记录，同时更新抽奖总数和能否抽奖的状态      INSERT INTO h5_game_play_log (game_ID,@source);      UPDATE  h5_game_player SET status=1,played_num=2 WHERE player_code = @weixin_ID;      --去抽奖       GOTO action_draw;    END	--返回信息	goto action_msg_end;	--抽奖	action_draw:    BEGIN				SELECT @rnd=cast( floor(rand()*9999) as int);  				IF(@rnd<500)					 BEGIN							 --SELECT @lot_ID = (SELECT top 1 ID FROM h5_game_award_pool WHERE award_ID=@lot_type AND action_ID=0 AND game_ID=@game_ID AND start_date < GETDATE());							 SELECT @lot_ID = (SELECT top 1 p.ID FROM h5_game_award_pool p left JOIN h5_game_award_config f ON p.award_ID = f.ID WHERE f.award_code=@lot_type AND p.action_ID=0 AND p.game_ID=@game_ID AND p.start_date < GETDATE());							 IF(@lot_ID>0)									BEGIN											UPDATE h5_game_award_pool SET action_ID=1,player_code=@weixin_ID,updated=GETDATE() WHERE ID = @lot_ID;                      --points作为是否中奖的标识1：中奖，0：未中奖                      UPDATE  h5_game_player SET points=1 WHERE player_code = @weixin_ID;											SELECT 'true' as success,0 AS code,'恭喜您中奖了' as msg,@weixin_ID as weixin_ID,exchange_code as lot_code,shop_name as brand_name,award_Title,comments AS award_name,award_price,award_uri FROM h5_game_award_pool p                          left JOIN h5_game_award_config f ON  p.award_ID = f.ID WHERE p.ID=@lot_ID											return									END 													 END				 ELSE					 BEGIN						 SELECT 'true' as success,'您没有中奖了' as msg,'' As lot_code,'' AS weixin_ID						 return					 END    END;			--错误，无此方法	action_error:		--print @action		print 'error'		SELECT @success as success,@code AS code,@msg as msg,'' AS weixin_ID		RETURN			--直接结束	action_ok:		print 'ok:' + @action + ' ' + @weixin_ID		RETURN	--显示返回信息，并结束	action_msg_end:		SET @success='true'		print 'ok:' + @action + ' ' + @weixin_ID		SELECT @success as success,'' AS weixin_ID		RETURN	SET NOCOUNT OFF;  END

 

  sqlServer的存储过程调用跟MysqL是有区别的：

      exec h5_newyear_app_draw '%s',%s,'%s','%s' 不能带括号

  MysqL是：

      call prc_add_si_member_point(1,'%s'); 带括号

总结

以上是内存溢出为你收集整理的SQLServer存储过程全部内容，希望文章能够帮你解决SQLServer存储过程所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。