Миграция хранимых процедур T-SQL MSSQL → MySQL

Примеры портирования хранимых процедур T-SQL на MySQL: параметры OUT, BIT, IMAGE, SCOPE_IDENTITY и динамический INSERT в связке документов и полей.

CreateDocument

На MSSQL:

ALTER PROCEDURE [dbo].[CreateDocument]
  @idedo INT = NULL,
  @sigOne INT = NULL,
  @sigTwo INT = NULL,
  @keyOne nvarchar(50),
  @keyTwo nvarchar(50),
  @fileName nvarchar(50),
  @fileContent image,
  @id INT OUT
AS
BEGIN

  IF @sigOne = NULL
  BEGIN
  SET @sigOne = CONVERT(bit,@sigOne)
  END

  IF @sigTwo = NULL
  BEGIN
  SET @sigTwo = CONVERT(bit,@sigTwo)
  END

  INSERT INTO [EDO].[dbo].[Document]           ([IDEDO],[SigOne],[SigTwo],[KeyOne],[KeyTwo],[NameFile],[FileBody], [IsError])
     VALUES                                         (@idedo,@sigOne,@sigTwo,@keyOne,@keyTwo,@fileName,@fileContent, 0)

     SET @id = SCOPE_IDENTITY()
END

Эквивалент на MySQL:

DROP PROCEDURE IF EXISTS `CreateDocument`;

DELIMITER $$

CREATE PROCEDURE `CreateDocument`(
  IN _idedo INT,
  IN _sigOne INT,
  IN _sigTwo INT,
  IN _keyOne VARCHAR(50),
  IN _keyTwo VARCHAR(50),
  IN _nameFile VARCHAR(50),
  IN _fileContent LONGBLOB,
  OUT _id INT
)
BEGIN

  DECLARE sigOne INT;
  DECLARE sigTwo INT;

  IF _sigOne IS NULL THEN
    SET sigOne = 0;
  ELSE
    SET sigOne = _sigOne;
  END IF;

  IF _sigTwo IS NULL THEN
    SET sigTwo = 0;
  ELSE
    SET sigTwo = _sigTwo;
  END IF;

  INSERT INTO `Document` (`IDEDO`, `SigOne`, `SigTwo`, `KeyOne`, `KeyTwo`, `NameFile`, `FileBody`, `IsError`)
    VALUES (_idedo, sigOne, sigTwo, _keyOne, _keyTwo, _nameFile, _fileContent, 0);

    SELECT LAST_INSERT_ID() INTO _id;
END$$

DELIMITER ;

UpdateDocument

На MSSQL:

ALTER PROCEDURE [dbo].[UpdateDocument]
  @sigOne bit = 0,
  @sigTwo bit = 0,
  @keyOne nvarchar(50),
  @keyTwo nvarchar(50),
  @fileName nvarchar(50),
  @fileContent image,
  @id INT
AS
BEGIN

  UPDATE [EDO].[dbo].[Document] SET  [SigOne]=@sigOne, [SigTwo]=@sigTwo, [KeyOne]=@keyOne, [KeyTwo]=@keyTwo, [NameFile]=@fileName,[FileBody]=@fileContent
    WHERE  [id]=@id

END

На MySQL:

DROP PROCEDURE IF EXISTS `UpdateDocument`;

DELIMITER $$


CREATE PROCEDURE `UpdateDocument` (
  IN _sigOne INT,
  IN _sigTwo INT,
  IN _keyOne VARCHAR(50),
  IN _keyTwo VARCHAR(50),
  IN _nameFile VARCHAR(50),
  IN _fileContent LONGBLOB,
  IN _id INT
)
BEGIN

  UPDATE `Document` SET `SigOne` = _sigOne, `SigTwo` = _sigTwo, `KeyOne` = _keyOne,
    `KeyTwo` = _keyTwo, `NameFile` = _nameFile, `FileBody` = _fileContent
    WHERE `ID` = _id;

END$$

DELIMITER ;

SetField

На MSSQL:

ALTER PROCEDURE [dbo].[SetField]
  @docId INT,
  @fieldName nvarchar(50),
  @fieldDisplayName nvarchar(50),
  @fieldType nvarchar(2),
  @fieldValue nvarchar(50)
AS
BEGIN

    DECLARE @fieldId INT = 0

    SELECT @fieldId = ID FROM [EDO].[dbo].[FIELD] WHERE InnerName = @fieldName

    IF(@fieldId = 0)
    BEGIN
    INSERT INTO [EDO].[dbo].[FIELD] ([InnerName],[DisplayName],[FieldType])
    VALUES                 (@fieldName,@fieldDisplayName,@fieldType)

    SET @fieldId = SCOPE_IDENTITY()
    END


    INSERT INTO [EDO].[dbo].[FieldMapping]      ([DocumentID],[FieldID],[FieldValue])
    VALUES                       (@docId,@fieldId,@fieldValue)

END

На MySQL (с PREPARE для подстановки):

DROP PROCEDURE IF EXISTS `SetField`;

DELIMITER $$


CREATE PROCEDURE `SetField` (
  IN _docId INT,
  IN _fieldName CHAR(50),
  IN _fieldDisplayName CHAR(50),
  IN _fieldType CHAR(2),
  IN _fieldValue VARCHAR(50)
)
BEGIN

    SET @fieldId = 0;
    SET @docId = _docId;
    SET @fieldValue = _fieldValue;

    SET @fieldId = (SELECT `ID` FROM `Field` WHERE InnerName = _fieldName);

    IF @fieldId IS NULL THEN
      INSERT INTO `Field` (`InnerName`, `DisplayName`, `FieldType`)
        VALUES (_fieldName, _fieldDisplayName, _fieldType);
      SELECT LAST_INSERT_ID() INTO @fieldId;
    END IF;

    PREPARE stmt1 FROM 'INSERT INTO `FieldMapping` (`DocumentID`, `FieldID`, `FieldValue`) VALUES (?, ?, ?)';

    EXECUTE stmt1 USING @docId, @fieldId, @fieldValue;

    DEALLOCATE PREPARE stmt1;

END$$

DELIMITER ;

UpdateField

На MSSQL:

ALTER PROCEDURE [dbo].[UpdateField]

  @docId INT,
  @fieldId INT,
  @fieldValue nvarchar(50)
AS
BEGIN
  DECLARE @fieldIdTemp INT
  SELECT @fieldIdTemp = ID FROM [EDO].[dbo].[FieldMapping] WHERE [DocumentID] = @docId  AND [FieldID] = @fieldId

    IF(@fieldIdTemp = 0) BEGIN
    INSERT INTO [EDO].[dbo].[FieldMapping]      ([DocumentID],[FieldID],[FieldValue])
    VALUES                       (@docId,@fieldId,@fieldValue)
    END ELSE BEGIN
      UPDATE [EDO].[dbo].[FieldMapping] SET  [FieldValue]=@fieldValue
      WHERE [DocumentID] = @docId  AND [FieldID] = @fieldId
    END

END

На MySQL:

DROP PROCEDURE IF EXISTS `UpdateField`;

DELIMITER $$


CREATE PROCEDURE `UpdateField` (
  IN _docId INT,
  IN _fieldId INT,
  IN _fieldValue VARCHAR(50)
)
BEGIN

    IF NOT EXISTS (SELECT * FROM `FieldMapping` WHERE `DocumentID` = _docId AND `FieldID` = _fieldId) THEN
      INSERT INTO `FieldMapping` (`DocumentID`, `FieldID`, `FieldValue`)
        VALUES (_docId, _fieldId, _fieldValue);
    ELSE
      UPDATE `FieldMapping` SET `FieldValue` = _fieldValue
        WHERE `DocumentID` = _docId  AND `FieldID` = _fieldId;
    END IF;

END$$

DELIMITER ;