使用Spring SimpleJdbcCall调用带默认参数的SQL Server存储过程报错求助

我尝试用Spring的SimpleJdbcCall调用SQL Server中带有默认值参数的存储过程，未提供该默认参数时执行失败并报错，希望排查代码问题。使用的数据库为SQL Server。

目标存储过程 #

CREATE PROCEDURE createUser
    @address varchar(250) = NULL,
    @name varchar(25) ,
    @Id numericid = 0 OUTPUT
AS
BEGIN
    INSERT INTO TCustomer (name, address) 
    VALUES (@name, @address)

    SELECT @Id = id 
    FROM TCustomer 
    WHERE name = @name AND address = @address

    SELECT @Id

    RETURN 0    
END

尝试的实现方式及报错 #

场景1：使用withNamedBinding()，输入变量名带@符号 #

public int createUser(){
        SimpleJdbcCall spCall = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("createUser")
                .withSchemaName("dbo")
                .withoutProcedureColumnMetaDataAccess()
                .useInParameterNames("@name")
                .withNamedBinding()
                .declareParameters(new SqlParameter("@name", Types.VARCHAR),
                        new SqlOutParameter("@Id", Types.NUMERIC));
      Map<String, Object> in = new HashMap<>();
      in.put("name","Rahul");

      Map<String, Object> retValue = spCall.execute(in);
}

报错信息：org.springframework.dao.InvalidDataAccessApiUsageException: 必填输入参数'@name'缺失。

场景2：使用withNamedBinding()，输入变量名不带@符号 #

public int createUser(){
        SimpleJdbcCall spCall = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("createUser")
                .withSchemaName("dbo")
                .withoutProcedureColumnMetaDataAccess()
                .useInParameterNames("name")
                .withNamedBinding()
                .declareParameters(new SqlParameter("name", Types.VARCHAR),
                        new SqlOutParameter("Id", Types.NUMERIC));

      Map<String, Object> in = new HashMap<>();
      in.put("name","Rahul");

      Map<String, Object> retValue = spCall.execute(in);
}

报错信息：org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; SQL语法错误 [{call createUser(name => ?, Id => ?)}]; 嵌套异常为java.sql.SQLException: 参数索引1无效。

场景3：不使用withNamedBinding()，输入变量名不带@符号 #

public int createUser(){
        SimpleJdbcCall spCall = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("createUser")
                .withSchemaName("dbo")
                .withoutProcedureColumnMetaDataAccess()
                .useInParameterNames("name")
                .declareParameters(new SqlParameter("name", Types.VARCHAR),
                        new SqlOutParameter("Id", Types.NUMERIC));

      Map<String, Object> in = new HashMap<>();
      in.put("name","Rahul");

      Map<String, Object> retValue = spCall.execute(in);
}

报错信息：org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; SQL语法错误 [{call TCustomer(?, ?)}]; 嵌套异常为java.sql.SQLException: 形式参数"@name"未声明为OUTPUT参数，但传入的实际参数要求输出。

场景4：不使用withNamedBinding()，输入变量名带@符号 #

public int createUser(){
        SimpleJdbcCall spCall = new SimpleJdbcCall(jdbcTemplate)
                .withProcedureName("createUser")
                .withSchemaName("dbo")
                .withoutProcedureColumnMetaDataAccess()
                .useInParameterNames("@name")
                .declareParameters(new SqlParameter("@name", Types.VARCHAR),
                        new SqlOutParameter("@Id", Types.NUMERIC));

      Map<String, Object> in = new HashMap<>();
      in.put("name","Rahul");

      Map<String, Object> retValue = spCall.execute(in);
}

报错信息：org.springframework.dao.InvalidDataAccessApiUsageException: 必填输入参数'@name'缺失。

内容的提问来源于stack exchange，提问作者Rahul