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)=''
)
AS
BEGIN
  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_error

action_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'); 带括号

（编辑：李大同）

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