我有这个存储过程：
USE [MYDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_UPLOAD_MOVTIT_Update]
( @IN_ID_RECORD nvarchar(2)
, @IN_COD_IST nvarchar(5)
, @IN_COD_SOC_GEST char(5)
, @IN_COD_PTF nvarchar(2)
, @IN_COD_LIN nvarchar(2)
, @IN_COD_GEST nvarchar(5)
, @IN_DAT_RIF nvarchar(10)
, @IN_DAT_VAL nvarchar(10)
, @IN_ID_UPLOAD nvarchar(2)
, @IN_COD_CAUS nvarchar(6)
, @IN_VAL_COD_TIT nvarchar(30)
, @IN_DIV_RIF nvarchar(3)
, @IN_IMP numeric(25,3)
, @IN_IMP_RATEO decimal(18,3)
, @IN_QTA numeric(18,5)
, @IN_PRZ decimal(21, 10) = 0
, @IN_FX decimal(18, 9)
, @IN_COD_MER nvarchar(20)
, @IN_NOTE nvarchar(100))
AS
DECLARE @@SQL nvarchar(max)
, @@TABLE_NAME nvarchar(45)
, @@WHERE nvarchar(max)
, @@DAT_RIF smalldatetime
, @@DAT_VAL smalldatetime
SET NOCOUNT ON
SET @@TABLE_NAME = 'UPLOAD_MOVTIT'
SET @@TABLE_NAME = @@TABLE_NAME + '_' + @IN_COD_SOC_GEST
SET @@WHERE = ' WHERE'
IF (LTRIM(@IN_ID_RECORD) <> '')
SET @@WHERE = @@WHERE + ' AND ID_RECORD = '+@IN_ID_RECORD
IF (LTRIM(@IN_COD_IST) <> '')
SET @@WHERE = @@WHERE + ' AND COD_IST = @IN_COD_IST'
IF (LTRIM(@IN_COD_SOC_GEST) <> '')
SET @@WHERE = @@WHERE + ' AND COD_SOC_GEST = @IN_COD_SOC_GEST'
--IF (LTRIM(@IN_COD_PTF) <> '')
-- SET @@WHERE = @@WHERE + ' AND COD_PTF = @IN_COD_PTF'
--IF (LTRIM(@IN_COD_LIN) <> '')
-- SET @@WHERE = @@WHERE + ' AND COD_LIN = @IN_COD_LIN'
--IF (LTRIM(@IN_COD_GEST) <> '')
-- SET @@WHERE = @@WHERE + ' AND COD_GEST = @IN_COD_GEST'
IF (LTRIM(@IN_DAT_RIF) <> '')
SET @@DAT_RIF = CONVERT(SMALLDATETIME, LTRIM(@IN_DAT_RIF), 103)
IF (LTRIM(@IN_DAT_VAL) <> '')
SET @@DAT_VAL = CONVERT(SMALLDATETIME, LTRIM(@IN_DAT_VAL), 103)
IF (LTRIM(@IN_ID_UPLOAD) <> '')
SET @@WHERE = @@WHERE + ' AND ID_UPLOAD = '+@IN_ID_UPLOAD
IF (@@WHERE <> ' WHERE')
SET @@WHERE = REPLACE(@@WHERE, ' WHERE AND', ' WHERE')
ELSE
SET @@WHERE = ''
BEGIN
SET @@SQL = 'UPDATE ' + @@TABLE_NAME
+ ' SET COD_PTF = @IN_COD_PTF
, COD_LIN = @IN_COD_LIN
, COD_GEST = @IN_COD_GEST
, DAT_RIF = @@DAT_RIF
, DAT_VAL = @@DAT_VAL
, COD_CAUS = @IN_COD_CAUS
, VAL_COD_TIT = @IN_VAL_COD_TIT
, DIV_RIF = @IN_DIV_RIF
, IMP = @IN_IMP
, IMP_RATEO = @IN_IMP_RATEO
, QTA = @IN_QTA
, PRZ = @IN_PRZ
, FX = @IN_FX
, COD_MER = @IN_COD_MER
, NOTE = @IN_NOTE
, DAT_ULT_MOD = CURRENT_TIMESTAMP ' + @@WHERE
END
PRINT @@sql
EXEC sp_executesql @@SQL
, N'@IN_ID_RECORD nvarchar(2), @IN_COD_IST nvarchar(5), @IN_COD_SOC_GEST char(5), @IN_COD_PTF nvarchar(2), @IN_COD_LIN nvarchar(2), @IN_COD_GEST nvarchar(5), @IN_DAT_RIF nvarchar(10), @IN_DAT_VAL nvarchar(10), @IN_COD_CAUS nvarchar(6), @IN_VAL_COD_TIT nvarchar(30), @IN_DIV_RIF nvarchar(3), @IN_ID_UPLOAD nvarchar(2), @IN_IMP numeric(25,3), @IN_IMP_RATEO decimal(18,3), @IN_QTA numeric(18,5), @IN_PRZ decimal(21, 10), @IN_FX decimal(18, 9), @IN_COD_MER nvarchar(20), @IN_NOTE nvarchar(100), @@DAT_RIF smalldatetime, @@DAT_VAL smalldatetime'
, @IN_ID_RECORD, @IN_COD_IST, @IN_COD_SOC_GEST, @IN_COD_PTF, @IN_COD_LIN, @IN_COD_GEST, @IN_DAT_RIF, @IN_DAT_VAL, @IN_COD_CAUS, @IN_VAL_COD_TIT, @IN_DIV_RIF, @IN_ID_UPLOAD, @IN_IMP, @IN_IMP_RATEO, @IN_QTA, @IN_PRZ, @IN_FX, @IN_COD_MER, @IN_NOTE,@@DAT_RIF, @@DAT_VAL
RETURN @@ERROR
我将其映射如下：
public class UploadMovTitUpdateStoredProcedure extends StoredProcedure {
private static final String SPROC_NAME = "sp_UPLOAD_MOVTIT_Update";
public UploadMovTitUpdateStoredProcedure(JdbcTemplate jdbcTemplate) {
super(jdbcTemplate, SPROC_NAME);
declareParameter(new SqlReturnResultSet("result", new RowMapper<UploadMovTitSave>() {
@Override
public UploadMovTitSave mapRow(ResultSet rs, int rowNum) throws SQLException {
UploadMovTitSave movTit = new UploadMovTitSave();
return movTit;
}
}));
declareParameter(new SqlParameter("IN_ID_RECORD", Types.VARCHAR));
declareParameter(new SqlParameter("IN_COD_IST", Types.VARCHAR));
declareParameter(new SqlParameter("IN_COD_SOC_GEST", Types.CHAR));
declareParameter(new SqlParameter("IN_ID_UPLOAD", Types.VARCHAR));
declareParameter(new SqlParameter("IN_COD_PTF", Types.VARCHAR));
declareParameter(new SqlParameter("IN_COD_LIN", Types.VARCHAR));
declareParameter(new SqlParameter("IN_COD_GEST", Types.VARCHAR));
declareParameter(new SqlParameter("IN_DAT_RIF", Types.VARCHAR));
declareParameter(new SqlParameter("IN_DAT_VAL", Types.VARCHAR));
declareParameter(new SqlParameter("IN_COD_CAUS", Types.VARCHAR));
declareParameter(new SqlParameter("IN_VAL_COD_TIT", Types.VARCHAR));
declareParameter(new SqlParameter("IN_DIV_RIF", Types.VARCHAR));
declareParameter(new SqlParameter("IN_IMP", Types.NUMERIC));
declareParameter(new SqlParameter("IN_IMP_RATEO", Types.DECIMAL));
declareParameter(new SqlParameter("IN_QTA", Types.NUMERIC));
declareParameter(new SqlParameter("IN_PRZ", Types.DECIMAL));
declareParameter(new SqlParameter("IN_FX", Types.DECIMAL));
declareParameter(new SqlParameter("IN_COD_MER", Types.VARCHAR));
declareParameter(new SqlParameter("IN_NOTE", Types.VARCHAR));
compile();
}
}
调用存储过程的"dao"如下所示：
public UploadMovTitSave updateMovTitDetail(UploadMovTitSave entity) {
UploadMovTitUpdateStoredProcedure sproc = new UploadMovTitUpdateStoredProcedure(jdbcTemplate);
HashMap<String, Serializable> params = new HashMap<String, Serializable>();
DecimalFormatSymbols symbols = new DecimalFormatSymbols();
symbols.setGroupingSeparator('.');
symbols.setDecimalSeparator(',');
String pattern = "#,##0.0#";
DecimalFormat decimalFormat = new DecimalFormat(pattern, symbols);
decimalFormat.setParseBigDecimal(true);
try {
params.put("IN_ID_RECORD", entity.getIdRecord());
params.put("IN_COD_IST", (entity.getCodIst()!=null ? entity.getCodIst() : EMPTY_STRING));
params.put("IN_COD_SOC_GEST", (entity.getCodSocGest()!=null ? entity.getCodSocGest() : EMPTY_STRING));
params.put("IN_COD_PTF", (entity.getCodPtf()!=null ? entity.getCodPtf() : EMPTY_STRING));
params.put("IN_COD_LIN", (entity.getCodLin()!=null ? entity.getCodLin() : EMPTY_STRING));
params.put("IN_COD_GEST", (entity.getCodGest()!=null ? entity.getCodGest() : EMPTY_STRING));
params.put("IN_DAT_RIF", (entity.getDatRif()!=null ? entity.getDatRif().trim() : EMPTY_STRING));
params.put("IN_DAT_VAL", (entity.getDatVal()!=null ? entity.getDatVal().trim() : EMPTY_STRING));
params.put("IN_ID_UPLOAD", entity.getIdUpload());
params.put("IN_COD_CAUS", (entity.getCodCaus()!=null ? entity.getCodCaus() : EMPTY_STRING));
params.put("IN_VAL_COD_TIT", (entity.getValCodTit()!=null ? entity.getValCodTit() : EMPTY_STRING));
params.put("IN_DIV_RIF", (entity.getDivRif()!=null ? entity.getDivRif() : EMPTY_STRING));
params.put("IN_IMP", (BigDecimal) decimalFormat.parse((String)entity.getImp()));
params.put("IN_IMP_RATEO", (BigDecimal) decimalFormat.parse((String)entity.getImpRateo()));
params.put("IN_QTA", (BigDecimal) decimalFormat.parse((String)entity.getQta()));
params.put("IN_PRZ", (BigDecimal) decimalFormat.parse((String)entity.getPrz()));
params.put("IN_FX", (BigDecimal) decimalFormat.parse((String)entity.getFx()));
params.put("IN_COD_MER", (entity.getCodMer()!=null ? entity.getCodMer() : EMPTY_STRING));
params.put("IN_NOTE", (entity.getNote()!=null ? entity.getNote() : EMPTY_STRING));
} catch (ParseException e) {
// TODO Auto-generated catch block
log.error("Couldn't parse BigDecimals", e);
}
log.error("updateMovTitDetail - parametri input : " + params.toString());
Map<String, Object> results = new HashMap<String, Object>();
try{
results = sproc.execute(params);
}catch(DataIntegrityViolationException ex){
log.error(ex.getMessage());
log.error(ex.getStackTrace().toString());
}
UploadMovTitSave movTit = (UploadMovTitSave) results.get("result");
return movTit;
}
当我使用从前端传递的相同参数从 SQL Server Management Studio 运行存储过程时，存储过程返回 0，因此没有错误。
此执行的示例：
USE [mydb]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[sp_UPLOAD_MOVTIT_Update]
@IN_ID_RECORD = N'44',
@IN_COD_IST = N'0',
@IN_COD_SOC_GEST = N'90000',
@IN_COD_PTF = N'20',
@IN_COD_LIN = N'22',
@IN_COD_GEST = N'00222',
@IN_DAT_RIF = N'14/06/2017',
@IN_DAT_VAL = N'18/04/2017',
@IN_ID_UPLOAD = N'37',
@IN_COD_CAUS = N'S',
@IN_VAL_COD_TIT = N'IE00B1W4R501',
@IN_DIV_RIF = N'USD',
@IN_IMP = 1022924.19,
@IN_IMP_RATEO = 0.00,
@IN_QTA = 66000.00,
@IN_PRZ = 15.50,
@IN_FX = 1.07,
@IN_COD_MER = N' ',
@IN_NOTE = N' '
SELECT 'Return Value' = @return_value
GO
但是当我从软件运行它时，我收到一条dataintegrytiviolationexception，其中包含以下消息：
CallableStatementCallback; SQL [{call sp_UPLOAD_MOVTIT_Update(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; Conversion failed when converting character string to smalldatetime data type.; nested exception is java.sql.SQLException: Conversion failed when converting character string to smalldatetime data type.
我发现当我在存储过程中打印参数时，有一个参数切换IN_COD_GEST打印出来而不是IN_DAT_RIF。
问题在于声明参数的顺序，要声明的参数与它们实际放入参数列表中的顺序相同