Migrate Microsoft SQL Server T-SQL to Java

The code samples below provide you with more details.

• Microsoft SQL Server T-SQL

  1. CREATE PROCEDURE sp_demo_conversion
  2.     @DeptId INT,
  3.     @EmployeeCount INT OUTPUT   -- Output parameter
  4. AS
  5. BEGIN
  6.  
  7.     DECLARE @EmpId INT;
  8.     DECLARE @EmpName NVARCHAR(100);
  9.     DECLARE @CurrentDate DATETIME = GETDATE();
  10.  
  11.     PRINT 'Procedure started at ' + CONVERT(VARCHAR, @CurrentDate, 120);
  12.  
  13.     BEGIN TRY
  14.         -- Validate input parameter
  15.         IF @DeptId IS NULL OR @DeptId <= 0
  16.             THROW 50001, 'Invalid Department Id', 1;
  17.  
  18.         -- Count employees in department and return via output parameter
  19.         SELECT @EmployeeCount = COUNT(*)
  20.         FROM Employees
  21.         WHERE DeptId = @DeptId;
  22.  
  23.         PRINT 'Total employees in department: ' + CAST(@EmployeeCount AS VARCHAR);
  24.  
  25.         -- Exit early if no employees found
  26.         IF @EmployeeCount = 0
  27.         BEGIN
  28.             PRINT 'No employees found';
  29.             RETURN;
  30.         END
  31.  
  32.         -- Cursor to iterate employees
  33.         DECLARE emp_cursor CURSOR FOR
  34.             SELECT EmpId, EmpName
  35.             FROM Employees
  36.             WHERE DeptId = @DeptId;
  37.  
  38.         OPEN emp_cursor;
  39.  
  40.         FETCH NEXT FROM emp_cursor INTO @EmpId, @EmpName;
  41.  
  42.         WHILE @@FETCH_STATUS = 0
  43.         BEGIN
  44.             -- Conditional branch
  45.             IF LEN(@EmpName) > 10
  46.                 PRINT 'Long name: ' + @EmpName;
  47.             ELSE
  48.                 PRINT 'Short name: ' + @EmpName;
  49.  
  50.             -- Try to update employee
  51.             UPDATE Employees
  52.             SET LastUpdated = @CurrentDate
  53.             WHERE EmpId = @EmpId;
  54.  
  55.             FETCH NEXT FROM emp_cursor INTO @EmpId, @EmpName;
  56.         END
  57.  
  58.         CLOSE emp_cursor;
  59.         DEALLOCATE emp_cursor;
  60.     END TRY
  61.  
  62.     BEGIN CATCH
  63.         -- Collect error details
  64.         DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
  65.         DECLARE @ErrNum INT = ERROR_NUMBER();
  66.         DECLARE @ErrSeverity INT = ERROR_SEVERITY();
  67.         DECLARE @ErrState INT = ERROR_STATE();
  68.  
  69.         PRINT 'Error occurred: ' + @ErrMsg;
  70.  
  71.         -- Log error into a dedicated table
  72.         INSERT INTO ErrorLog (ErrorDate, ErrorNumber, Severity, State, Message)
  73.         VALUES (GETDATE(), @ErrNum, @ErrSeverity, @ErrState, @ErrMsg);
  74.  
  75.         -- Re-throw the error for higher-level handling
  76.         THROW;
  77.     END CATCH;
  78.  
  79.     PRINT 'Procedure finished at ' + CONVERT(VARCHAR, GETDATE(), 120);
  80. END

  → Java

  1. import com.sql4j.db.cursor.*;
  2. import com.sql4j.db.implementation.Dao;
  3. import com.sql4j.db.util.ResultSetService;
  4. import com.sql4j.exception.*;
  5. import com.sql4j.transaction.aop.TransactionStatus;
  6. import com.sql4j.util.*;
  7. import java.time.LocalDateTime;
  8. import java.util.*;
  9. import org.springframework.beans.factory.annotation.Autowired;
  10. import org.springframework.stereotype.Service;
  11. import org.springframework.web.context.annotation.RequestScope;
  12. @Service
  13. @RequestScope
  14. public class SpDemoConversion implements ISpDemoConversion {
  15.   @Autowired
  16.   private ErrorUtil errorUtil;
  17.   @Autowired
  18.   private ResultSetService resultSetService;
  19.   @Autowired
  20.   private Dao dao;
  21.   @Autowired
  22.   private CursorStatusService cursorStatusService;
  23.   @TransactionStatus
  24.   @Override
  25.   public Map< String, Object > spSpDemoConversion(Integer deptid, Integer employeecount) {
  26.     Map<String, Object> outData = new HashMap<>();
  27.     outData.put("retStatus", 0);
  28.  
  29.     try {
  30.       Integer empid = null;
  31.       String empname = null;
  32.       LocalDateTime currentdate = TimeUtil.getDate();
  33.       System.out.println(StringUtil.concatNullable("Procedure started at ", Converter.toString(currentdate, 120)));
  34.  
  35.       try {
  36.         errorUtil.beginProtectedBlock();
  37.  
  38.         if (deptid == null || ComparisonUtil.isLessOrEqual(deptid, 0)) {
  39.           errorUtil.throwError(50001, "Invalid Department Id", 1);
  40.         }
  41.  
  42.         dao.selectRes("SELECT COUNT(*) " +
  43.                       "FROM Employees " +
  44.                       "WHERE DeptId = :DEPTID ", "DEPTID", deptid);
  45.         System.out.println(StringUtil.concatNullable("Total employees in department: ", Converter.toString(employeecount)));
  46.  
  47.         if (ComparisonUtil.areEqual(employeecount, 0)) {
  48.           System.out.println("No employees found");
  49.           return outData;
  50.         }
  51.  
  52.         CursorReader empCursor = new CursorReader(cursorStatusService, true, "SELECT EmpId, EmpName " +
  53.                 "FROM Employees " +
  54.                 "WHERE DeptId = :DEPTID ", "DEPTID", deptid);
  55.         empCursor.open();
  56.  
  57.         if (empCursor.fetchNext()) {
  58.           empid = empCursor.getColumnValue(Integer.class);
  59.           empname = empCursor.getColumnValue(String.class);
  60.         }
  61.  
  62.         while (ComparisonUtil.areEqual(cursorStatusService.getFetchStatus(), 0)) { // Conditional branch
  63.           if (ComparisonUtil.isGreaterThan(StringUtil.length(empname), 10)) {
  64.             System.out.println(StringUtil.concatNullable("Long name: ", empname));
  65.           }
  66.           else {
  67.             System.out.println(StringUtil.concatNullable("Short name: ", empname));
  68.           }
  69.  
  70.           // Try to update employee
  71.           dao.update("UPDATE Employees " +
  72.                      "SET LastUpdated = :CURRENTDATE " +
  73.                      "WHERE EmpId = :EMPID ", "CURRENTDATE", currentdate, "EMPID", empid);
  74.  
  75.           if (empCursor.fetchNext()) {
  76.             empid = empCursor.getColumnValue(Integer.class);
  77.             empname = empCursor.getColumnValue(String.class);
  78.           }
  79.         }
  80.  
  81.         empCursor.close();
  82.         empCursor.deallocate();
  83.       }
  84.       catch (GeneralException e) {
  85.         errorUtil.failProtectedBlock();
  86.         String errmsg = e.getMessage();
  87.         Integer errnum = e.getErrorNumber();
  88.         Integer errseverity = e.getSeverity();
  89.         Integer errstate = e.getState();
  90.         System.out.println(StringUtil.concatNullable("Error occurred: ", errmsg));
  91.         dao.update("INSERT INTO ErrorLog (ErrorDate, ErrorNumber, Severity, State, Message) " +
  92.                    "VALUES (GETDATE(), :ERRNUM, :ERRSEVERITY, :ERRSTATE, :ERRMSG) ", "ERRNUM", errnum, "ERRSEVERITY", errseverity, "ERRSTATE", errstate, "ERRMSG", errmsg);
  93.         throw e;
  94.       }
  95.       finally {
  96.         errorUtil.finalizeProtectedBlock();
  97.       }
  98.  
  99.       System.out.println(StringUtil.concatNullable("Procedure finished at ", Converter.toString(TimeUtil.getDate(), 120)));
  100.       return outData;
  101.     }
  102.     finally {
  103.       outData.put("dataSet", resultSetService.getResultSet());
  104.       outData.put("employeecount", employeecount);
  105.     }
  106.   }
  107. }