sqlserver存储过程多个非嵌套select无法转换的问题

[CembZy]

当sqlserver存储过程有多个非嵌套select时，无法正确转换。 test case：

if (exists (select from sys.objects where name = 'GetUser')) drop proc GetUser
go create proc GetUser
@Id int as set nocount on;
begin SELECT e.employee_id, e.last_name, e.department_id FROM employees e, departments d WHERE e.department_id = d.department_id and e.Id=@Id; end;

转换后：

if (exists (select * from sys.objects where name = 'GetUser')) drop proc GetUser

分析源码：

  public int convert() {

        TGSqlParser sqlparser = new TGSqlParser(vendor);
        sqlparser.sqltext = this.query;
        ErrorNo = sqlparser.parse();
        if (ErrorNo != 0) {
            ErrorMessage = sqlparser.getErrormessage();
            return ErrorNo;
        }

        TCustomSqlStatement stmt = sqlparser.sqlstatements.get(0);
        analyzeSelect(stmt);
        String convertedQuery = stmt.toString();
        if (!convertedQuery.equals(this.query)) {
            converted = true;
        }
        this.query = convertedQuery;
        return ErrorNo;
    }

发现，sqlparser.sqlstatements.get(0)只是转换了第一个statement，如果select中只有一个table，就直接返回了不会进行再转换，this.query = convertedQuery;只是赋值了第一个被转换的statement，所以后面的内容没有被转换，并且缺失。 修改源码：

 private String totalQuery = "";
 public String getQuery() {
        return this.totalQuery.replaceAll("(?m)^[ \t]*\r?\n", "");
  }
 public int convert() {

        TGSqlParser sqlparser = new TGSqlParser(vendor);
        sqlparser.sqltext = this.query;
        ErrorNo = sqlparser.parse();
        if (ErrorNo != 0) {
            ErrorMessage = sqlparser.getErrormessage();
            return ErrorNo;
        }

        sqlparser.sqlstatements.forEachRemaining(it -> {
            analyzeSelect(it);
            String convertedQuery = it.toString();
            if (!convertedQuery.equals(this.query)) {
                converted = true;
                this.totalQuery += convertedQuery;
            }
            this.query = convertedQuery;
        });
        return ErrorNo;
    }

即可。