需要通过JDBC执行的SP代码如下:
MERGE SV_IB_WARRANTY_ST AS A
USING (
SELECT DISTINCT [MACHINE_TYPE]
,[SERIAL]
,[SEQUENCE_NUMBER]
,[WTY_ID]
,[WTY_START_DATE]
,[WTY_END_DATE]
,[BATCH_NUMBER]
,[SERVICE_PRODUCT_ID]
FROM [SV_IB_WARRANTY_ST_TEMP]
WHERE BATCH_NUMBER= @BATCH_NUMBER
)AS B
ON B.[MACHINE_TYPE]= A.[MACHINE_TYPE]
AND B.[SERIAL]= A.[SERIAL]
AND B.[SEQUENCE_NUMBER]=A.[SEQUENCE_NUMBER]
WHEN NOT MATCHED THEN
INSERT(
[MACHINE_TYPE]
,[SERIAL]
,[SEQUENCE_NUMBER]
,[WTY_ID]
,[WTY_START_DATE]
,[WTY_END_DATE]
,[BATCH_NUMBER]
,[SERVICE_PRODUCT_ID]
)
VALUES(
B.[MACHINE_TYPE]
,B.[SERIAL]
,B.[SEQUENCE_NUMBER]
,B.[WTY_ID]
,B.[WTY_START_DATE]
,B.[WTY_END_DATE]
,B.[BATCH_NUMBER]
,B.[SERVICE_PRODUCT_ID]
)
WHEN MATCHED THEN
UPDATE SET
A.[WTY_ID] =B.[WTY_ID]
,A.[WTY_START_DATE] =B.[WTY_START_DATE]
,A.[WTY_END_DATE] =B.[WTY_END_DATE]
,A.[BATCH_NUMBER] =B.[BATCH_NUMBER]
,A.[SERVICE_PRODUCT_ID] =B.[SERVICE_PRODUCT_ID];
报错信息:
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: A MERGE statement must be terminated by a semi-colon (;).
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:367)
at com.lenovo.lps.farseer.priest2.platform.util.DbExeUtils.execute(DbExeUtils.java:144)
at com.lenovo.lps.farseer.priest2.platform.util.DbExeUtils.exeSql(DbExeUtils.java:120)
... 83 more