存储过程USE [Member]
GO
/****** Object: StoredProcedure [dbo].[GetNextSeq] Script Date: 10/25/2016 14:23:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <>
-- Create date: <2016.3.31>
-- Description: <获取编号的下一个序列号>
-- =============================================
ALTER PROCEDURE [dbo].[GetNextSeq]
-- Add the parameters for the stored procedure here
@Code varchar(20), --序列编码
@Lenth smallint, --返回序列长度
@IsDate char(1), --是否在序列中包含日期信息 0：不包含 1：包含
@NewSeq char(14) output
AS
BEGIN
Declare @NextSeq int
Declare @CurrentDate char(8)
Declare @OldSeq int --表中存储的当前序列号
Declare @OldDate char(8) --返回表中当前的日期
--Declare @NewSeq varchar(20) --通过过程计算返回的最终编号
Declare @TabelSeq Table (NextSeq int) --表值变量，用于获得最新更新到表中的序列号
--判断传的序列编码值是否存在
If Not Exists(Select Code From Sequence Where Code =@Code)
Begin
Set @NewSeq ='-1'
Select '-1'
Return
End
--获取序列表中相关字段当前值
Select @OldDate =CurrentDate,@OldSeq =NextSeq From Sequence Where Code =@Code
--获取系统当前日期，将格式转换为20160331八位样式
Select @CurrentDate = convert(char(8),GETDATE(),112)
--不处理日期，表示取顺序流水号
If @IsDate = '0'
Begin
--将新的序列号写入表中
Update Sequence Set NextSeq =NextSeq +1 Output inserted.NextSeq into @TabelSeq Where Code =@Code
Select @NextSeq =NextSeq From @TabelSeq
Set @NewSeq =Right('0000000000000000000' +Ltrim(Rtrim(Convert(varchar(20),@NextSeq))),@Lenth)
select @NewSeq
End
--取日期，产生的流水号前八位为日期，后面为顺序流水号（输入参数的长度包括日期长度）
Else If @IsDate = '1'
Begin
If @OldDate =@CurrentDate
Begin
Update Sequence Set NextSeq =NextSeq +1 Output inserted.NextSeq into @TabelSeq Where Code =@Code
Select @NextSeq =NextSeq From @TabelSeq
Set @NewSeq =@CurrentDate +Right('0000000000000000000' +Ltrim(Rtrim(Convert(varchar(20),@NextSeq))),@Lenth -8)
select @NewSeq
End
Else
Begin
Update Sequence Set CurrentDate =@CurrentDate ,NextSeq =1 Where Code =@Code
Set @NewSeq =@CurrentDate +Right('00000000000000000001',@Lenth -8)
select @NewSeq
End
End
ENDmapper.xml<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.jkw100.ssm.mapper.CustomerMapperCustom" >
<select id="getNextSeq" parameterMap="getNextSeqMap" statementType="CALLABLE" resultMap="resultNextSeqMap">
CALL GetNextSeq(#{Code},#{Lenth},#{IsDate},#{NewSeq})
</select>
<parameterMap type="java.util.Map" id="getNextSeqMap">
<parameter property="Code" mode="IN" jdbcType="VARCHAR"/>
<parameter property="Lenth" mode="IN" jdbcType="SMALLINT"/>
<parameter property="IsDate" mode="IN" jdbcType="CHAR"/>
<parameter property="NewSeq" mode="OUT" jdbcType="CHAR"/>
</parameterMap>
<resultMap type="java.util.Map" id="resultNextSeqMap">
<result column="NewSeq" property="NewSeq" javaType="String" jdbcType="CHAR"/>
</resultMap>
</mapper>接口方法Map<String,Object> getNextSeq(Map<String, Object> map);Service接口public Map<String, Object> getNextSeq(Map<String, Object> map) throws Exception;Service实现类@Override
public Map<String, Object> getNextSeq(Map<String, Object> map) throws Exception {
return customerMapperCustom.getNextSeq(map);
}Controller方法@RequestMapping(value="/getNextSeq",method={RequestMethod.GET})
@ResponseBody
public MessageResult getNextSeq()
{
Map<String, Object> map = new HashMap<String, Object>();
map.put("Code","CustomerID");
map.put("Lenth", 12);
map.put("IsDate", "1");
map.put("NewSeq", "newSeq");
try {
System.out.println("getNextSeq:"+customerService.getNextSeq(map));
return MessageResult.ok();
} catch (Exception e) {
e.printStackTrace();
return MessageResult.build(1, e.getMessage());
}
}错误提示### Error querying database. Cause: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]'@P1' 附近有语法错误。
### The error may exist in com/jkw100/ssm/mapper/CustomerMapperCustom.xml
### The error may involve com.jkw100.ssm.mapper.CustomerMapperCustom.getNextSeqMap
### The error occurred while setting parameters
### SQL: CALL GetNextSeq(?,?,?,?)
### Cause: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]'@P1' 附近有语法错误。
; uncategorized SQLException for SQL []; SQL state [HY000]; error code [102]; [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]'@P1' 附近有语法错误。; nested exception is java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]'@P1' 附近有语法错误。",
添加回答
举报
0/150
提交
取消