SqlServer存储过程转MySQL存储过程

cmd1111 2016-03-17 05:04:28
SqlServer原存储过程:
USE ***
GO
/****** Object: StoredProcedure [dbo].[spb_ClubUserData_ChangeExperiencePoints] Script Date: 03/17/2016 17:05:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spb_ClubUserData_ChangeExperiencePoints]
(
@UserID int,
@ExperiencePoints int
)
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

if not exists (select * from spb_ClubUserDatas with (nolock) WHERE [UserID] = @UserID)
INSERT INTO spb_ClubUserDatas (UserID,ExperiencePoints)values(@UserID,@ExperiencePoints)
else
Update spb_ClubUserDatas set ExperiencePoints=ExperiencePoints+@ExperiencePoints where UserID=@UserID



转的MySQL存储过程:

BEGIN
#Routine body goes here...
set @UserID=UserID;
set @ExperiencePoints=ExperiencePoints;

if not exists (select * from spb_ClubUserDatas WHERE UserID = @UserID)
then
INSERT INTO spb_ClubUserDatas (UserID,ExperiencePoints)values(@UserID,@ExperiencePoints);
else
Update spb_ClubUserDatas set ExperiencePoints=ExperiencePoints+@ExperiencePoints where UserID=@;UserID
end if;
END;


我改的MySQL存储过程中要传的参数与字段相同,然后where后面的值就是相等的了,并不能起到筛选作用,这个需要怎么修改??如果我转的存储过程是错的,请大神指点一下

...全文
212 4 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
giftsf 2016-03-18
  • 打赏
  • 举报
回复
DELIMITER $$ USE `库名`$$ DROP PROCEDURE IF EXISTS `sp_test`$$ CREATE DEFINER=`root`@`%` PROCEDURE `sp_test`( IN p_UserID INT, IN p_ExperiencePoints INT ) BEGIN IF NOT EXISTS (SELECT 1 FROM spb_ClubUserDatas WHERE `UserID` = p_UserID) THEN INSERT INTO spb_ClubUserDatas(UserID,ExperiencePoints) VALUES(p_UserID,p_ExperiencePoints); ELSE UPDATE spb_ClubUserDatas SET ExperiencePoints=ExperiencePoints+p_ExperiencePoints WHERE UserID=p_UserID; END IF; END$$ DELIMITER ; -- 两个差别很大的
cmd1111 2016-03-18
  • 打赏
  • 举报
回复
引用 1 楼 AcHerat 的回复:
最后怎么是 where userid = @;userid 这里的分号是?
这个是粘贴错了的,分号应该在@UserID后面
AcHerat 2016-03-17
  • 打赏
  • 举报
回复
最后怎么是 where userid = @;userid 这里的分号是?

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧