mysql - 我怎样才能把它翻译成mysql？

Question

我最近从 sql server 迁移到 mysql，但在将我的 sql 存储过程转换为 mysql 存储过程时遇到了问题。这是我的 SQL SERVER 存储过程:

CREATE PROCEDURE dbo.GetGrades  
    @GradeId int = 0
    ,@Name nvarchar(100) = ''
AS
BEGIN   
    SET NOCOUNT ON;


    SELECT ISNULL(I.GradeId,0) as GradeId
      ,ISNULL(I.Name, '') as Name
      ,ISNULL(I.Details, '') as Details
      ,ISNULL(I.StatId, 0) as StatId
      ,ISNULL(I.CreatedById, 0) as CreatedById
      ,ISNULL(U.UserName, '') as CreatedByName
      ,ISNULL(I.CreatedOn, null) as CreatedOn

    FROM Grades I

        LEFT JOIN Users U ON U.UserId = I.CreatedById

    WHERE (I.GradeId = @GradeId OR @GradeId = 0)
      AND (I.Name LIKE '%' + @Name +'%' OR @Name = '')   
      AND I.StatId <> 2 

END

GO

这是我到目前为止在 mysql 存储过程中所做的:

DELIMITER $$

CREATE PROCEDURE GetGrades
(   
     IN intGradeId INT  
    ,IN strName VARCHAR(1000)       
)
BEGIN
SELECT 
      gradeId
    , gradeName
    , details
    , statId
    , createdById
    , createdOn 
FROM grades 
WHERE (gradeId = intGradeId OR intGradeId = 0)
AND (gradeName LIKE '%' + strName +'%' OR strName = '');
END

当我执行它时，这是错误信息:

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '+ strName +'%' OR strName = '')
AND statId = 1;
END' at line 16

还有，mysql有没有ISNULL()函数？

Answer 1

像这样:

DELIMITER $$

CREATE PROCEDURE GetGrades (   
     IN in_intGradeId INT, 
     IN in_strName VARCHAR(1000)       
)
BEGIN
    SELECT gradeId, gradeName, details, statId, createdById, createdOn 
    FROM grades 
    WHERE (gradeId = in_intGradeId OR in_intGradeId = 0) AND
          (gradeName LIKE CONCAT('%', in_strName, '%') OR in_strName = '');
END;