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 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;ELSEGOTO 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到8BEGININSERT 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,0,@lot_type);      --防止序列重复      IF NOT EXISTS (SELECT 1 FROM h5_game_play_log WHERE seq=@seq)         INSERT INTO h5_game_play_log (game_id,player_code,seq,created,from_code) VALUES(@game_id,@weixin_id,@seq,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,player_code,seq,created,from_code) VALUES(@game_id,@weixin_id,@seq,GETDATE(),@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:    BEGINSELECT @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)BEGINUPDATE 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_idreturnEND  END ELSE BEGIN SELECT 'true' as success,0 AS code,'您没有中奖了' as msg,'' As lot_code,'' AS weixin_id return END    END;--错误，无此方法action_error:--print @actionprint 'error'SELECT @success as success,@code AS code,@msg as msg,'' As lot_code,'' AS weixin_idRETURN--直接结束action_ok:print 'ok:' + @action + ' ' + @weixin_idRETURN--显示返回信息，并结束action_msg_end:SET @success='true'print 'ok:' + @action + ' ' + @weixin_idSELECT @success as success,@code AS code,@msg as msg,'' As lot_code,'' AS weixin_idRETURNSET NOCOUNT OFF;  END

 

  SQLServer的存储过程调用跟mysql是有区别的：

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

  mysql是：

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