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

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 ;

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 ;

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:

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 ;

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 ;