public void resolveBlock(
      CreateProcedureCommand command,
      Block block,
      GroupContext originalExternalGroups,
      TempMetadataAdapter original)
      throws QueryResolverException, QueryMetadataException, TeiidComponentException {
    LogManager.logTrace(
        org.teiid.logging.LogConstants.CTX_QUERY_RESOLVER,
        new Object[] {"Resolving block", block}); // $NON-NLS-1$

    // create a new variable and metadata context for this block so that discovered metadata is not
    // visible else where
    TempMetadataStore store = original.getMetadataStore().clone();
    TempMetadataAdapter metadata = new TempMetadataAdapter(original.getMetadata(), store);
    GroupContext externalGroups = new GroupContext(originalExternalGroups, null);

    // create a new variables group for this block
    GroupSymbol variables =
        ProcedureContainerResolver.addScalarGroup(
            ProcedureReservedWords.VARIABLES, store, externalGroups, new LinkedList<Expression>());

    for (Statement statement : block.getStatements()) {
      resolveStatement(command, statement, externalGroups, variables, metadata);
    }

    if (block.getExceptionGroup() != null) {
      // create a new variable and metadata context for this block so that discovered metadata is
      // not visible else where
      store = original.getMetadataStore().clone();
      metadata = new TempMetadataAdapter(original.getMetadata(), store);
      externalGroups = new GroupContext(originalExternalGroups, null);

      // create a new variables group for this block
      variables =
          ProcedureContainerResolver.addScalarGroup(
              ProcedureReservedWords.VARIABLES,
              store,
              externalGroups,
              new LinkedList<Expression>());
      isValidGroup(metadata, block.getExceptionGroup());

      if (block.getExceptionStatements() != null) {
        ProcedureContainerResolver.addScalarGroup(
            block.getExceptionGroup(), store, externalGroups, exceptionGroup, false);
        for (Statement statement : block.getExceptionStatements()) {
          resolveStatement(command, statement, externalGroups, variables, metadata);
        }
      }
    }
  }
  private TempMetadataStore resolveEmbeddedCommand(
      TempMetadataAdapter metadata, GroupContext groupContext, Command cmd)
      throws TeiidComponentException, QueryResolverException {
    QueryResolver.setChildMetadata(cmd, metadata.getMetadataStore(), groupContext);

    return QueryResolver.resolveCommand(cmd, metadata.getMetadata());
  }
  private void resolveStatement(
      CreateProcedureCommand command,
      Statement statement,
      GroupContext externalGroups,
      GroupSymbol variables,
      TempMetadataAdapter metadata)
      throws QueryResolverException, QueryMetadataException, TeiidComponentException {
    LogManager.logTrace(
        org.teiid.logging.LogConstants.CTX_QUERY_RESOLVER,
        new Object[] {"Resolving statement", statement}); // $NON-NLS-1$

    switch (statement.getType()) {
      case Statement.TYPE_IF:
        IfStatement ifStmt = (IfStatement) statement;
        Criteria ifCrit = ifStmt.getCondition();
        for (SubqueryContainer container :
            ValueIteratorProviderCollectorVisitor.getValueIteratorProviders(ifCrit)) {
          resolveEmbeddedCommand(metadata, externalGroups, container.getCommand());
        }
        ResolverVisitor.resolveLanguageObject(ifCrit, null, externalGroups, metadata);
        resolveBlock(command, ifStmt.getIfBlock(), externalGroups, metadata);
        if (ifStmt.hasElseBlock()) {
          resolveBlock(command, ifStmt.getElseBlock(), externalGroups, metadata);
        }
        break;
      case Statement.TYPE_COMMAND:
        CommandStatement cmdStmt = (CommandStatement) statement;
        Command subCommand = cmdStmt.getCommand();

        TempMetadataStore discoveredMetadata =
            resolveEmbeddedCommand(metadata, externalGroups, subCommand);

        if (subCommand instanceof StoredProcedure) {
          StoredProcedure sp = (StoredProcedure) subCommand;
          for (SPParameter param : sp.getParameters()) {
            switch (param.getParameterType()) {
              case ParameterInfo.OUT:
              case ParameterInfo.RETURN_VALUE:
                if (param.getExpression() != null && !isAssignable(metadata, param)) {
                  throw new QueryResolverException(
                      QueryPlugin.Event.TEIID30121,
                      QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30121, param.getExpression()));
                }
                sp.setCallableStatement(true);
                break;
              case ParameterInfo.INOUT:
                if (!isAssignable(metadata, param)) {
                  continue;
                }
                sp.setCallableStatement(true);
                break;
            }
          }
        }

        if (discoveredMetadata != null) {
          metadata.getMetadataStore().getData().putAll(discoveredMetadata.getData());
        }

        // dynamic commands need to be updated as to their implicitly expected projected symbols
        if (subCommand instanceof DynamicCommand) {
          DynamicCommand dynCommand = (DynamicCommand) subCommand;

          if (dynCommand.getIntoGroup() == null && !dynCommand.isAsClauseSet()) {
            if ((command.getResultSetColumns() != null && command.getResultSetColumns().isEmpty())
                || !cmdStmt.isReturnable()
                || command.getResultSetColumns() == null) {
              // we're not interested in the resultset
              dynCommand.setAsColumns(Collections.EMPTY_LIST);
            } else {
              // should match the procedure
              dynCommand.setAsColumns(command.getResultSetColumns());
            }
          }
        }

        if (command.getResultSetColumns() == null
            && cmdStmt.isReturnable()
            && subCommand.returnsResultSet()
            && subCommand.getResultSetColumns() != null
            && !subCommand.getResultSetColumns().isEmpty()) {
          command.setResultSetColumns(subCommand.getResultSetColumns());
          if (command.getProjectedSymbols().isEmpty()) {
            command.setProjectedSymbols(subCommand.getResultSetColumns());
          }
        }

        break;
      case Statement.TYPE_ERROR:
      case Statement.TYPE_ASSIGNMENT:
      case Statement.TYPE_DECLARE:
      case Statement.TYPE_RETURN:
        ExpressionStatement exprStmt = (ExpressionStatement) statement;
        // first resolve the value.  this ensures the value cannot use the variable being defined
        if (exprStmt.getExpression() != null) {
          Expression expr = exprStmt.getExpression();
          for (SubqueryContainer container :
              ValueIteratorProviderCollectorVisitor.getValueIteratorProviders(expr)) {
            resolveEmbeddedCommand(metadata, externalGroups, container.getCommand());
          }
          ResolverVisitor.resolveLanguageObject(expr, null, externalGroups, metadata);
        }

        // second resolve the variable
        switch (statement.getType()) {
          case Statement.TYPE_DECLARE:
            collectDeclareVariable(
                (DeclareStatement) statement, variables, metadata, externalGroups);
            break;
          case Statement.TYPE_ASSIGNMENT:
            AssignmentStatement assStmt = (AssignmentStatement) statement;
            ResolverVisitor.resolveLanguageObject(
                assStmt.getVariable(), null, externalGroups, metadata);
            if (!metadata.elementSupports(
                assStmt.getVariable().getMetadataID(), SupportConstants.Element.UPDATE)) {
              throw new QueryResolverException(
                  QueryPlugin.Event.TEIID30121,
                  QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30121, assStmt.getVariable()));
            }
            // don't allow variable assignments to be external
            assStmt.getVariable().setIsExternalReference(false);
            break;
          case Statement.TYPE_RETURN:
            ReturnStatement rs = (ReturnStatement) statement;
            if (rs.getExpression() != null) {
              if (command.getReturnVariable() == null) {
                throw new QueryResolverException(
                    QueryPlugin.Event.TEIID31125,
                    QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31125, rs));
              }
              rs.setVariable(command.getReturnVariable().clone());
            }
            // else - we don't currently require the use of return for backwards compatibility
            break;
        }

        // third ensure the type matches
        if (exprStmt.getExpression() != null) {
          Class<?> varType = exprStmt.getExpectedType();
          Class<?> exprType = exprStmt.getExpression().getType();
          if (exprType == null) {
            throw new QueryResolverException(
                QueryPlugin.Event.TEIID30123, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30123));
          }
          String varTypeName = DataTypeManager.getDataTypeName(varType);
          exprStmt.setExpression(
              ResolverUtil.convertExpression(exprStmt.getExpression(), varTypeName, metadata));
          if (statement.getType() == Statement.TYPE_ERROR) {
            ResolverVisitor.checkException(exprStmt.getExpression());
          }
        }
        break;
      case Statement.TYPE_WHILE:
        WhileStatement whileStmt = (WhileStatement) statement;
        Criteria whileCrit = whileStmt.getCondition();
        for (SubqueryContainer container :
            ValueIteratorProviderCollectorVisitor.getValueIteratorProviders(whileCrit)) {
          resolveEmbeddedCommand(metadata, externalGroups, container.getCommand());
        }
        ResolverVisitor.resolveLanguageObject(whileCrit, null, externalGroups, metadata);
        resolveBlock(command, whileStmt.getBlock(), externalGroups, metadata);
        break;
      case Statement.TYPE_LOOP:
        LoopStatement loopStmt = (LoopStatement) statement;
        String groupName = loopStmt.getCursorName();

        isValidGroup(metadata, groupName);
        Command cmd = loopStmt.getCommand();
        resolveEmbeddedCommand(metadata, externalGroups, cmd);
        List<Expression> symbols = cmd.getProjectedSymbols();

        // add the loop cursor group into its own context
        TempMetadataStore store = metadata.getMetadataStore().clone();
        metadata = new TempMetadataAdapter(metadata.getMetadata(), store);
        externalGroups = new GroupContext(externalGroups, null);

        ProcedureContainerResolver.addScalarGroup(groupName, store, externalGroups, symbols, false);

        resolveBlock(command, loopStmt.getBlock(), externalGroups, metadata);
        break;
      case Statement.TYPE_COMPOUND:
        resolveBlock(command, (Block) statement, externalGroups, metadata);
        break;
    }
  }