jdbc调用sqlserver存储过程,抛出异常:**com.microsoft.sqlserver.jdbc.SQLServerException:列名或所提供值的数目与表定义不匹配**。
以下是存储过程脚本:(调用时type传的1,执行查询动作)
CREATE PROCEDURE [dbo].[USP_GATEWAY_SELECT_USERREG]
(
@USER_OPENID varchar(30),
@TYPE int
)
AS
BEGIN
SET NOCOUNT ON
if(@TYPE = 1)
begin
select top 1 * from TBL_USER_REG where USER_OPENID = @USER_OPENID
end
else if(@TYPE = 2)
begin
INSERT INTO TBL_USER_REG_HIS (
CLIENT_ACCOUNT,
USER_OPENID
,ACCOUNT_TYPE
,ACCOUNT_NUM
,ACCOUNT_PWD
,USER_MBLPHONE
,IDCARD_NUM
,USER_NAME
,DEPT_NAME
,CLIENT_MANAGER
,USER_GENDER
,USER_AGE
,DEPT_CODE
,CREATED_BY
,created_time
,UPDATED_BY
,UPDATED_TIME
,CUSTOMER_ID
,REMARK )
SELECT
CLIENT_ACCOUNT
,USER_OPENID
,ACCOUNT_TYPE
,ACCOUNT_NUM
,ACCOUNT_PWD
,USER_MBLPHONE
,IDCARD_NUM
,USER_NAME
,DEPT_NAME
,CLIENT_MANAGER
,USER_GENDER
,USER_AGE
,DEPT_CODE
,CREATED_BY
,created_time
,UPDATED_BY
,GETDATE()
,CUSTOMER_ID
,REMARK
FROM TBL_USER_REG where USER_OPENID = @USER_OPENID
delete TBL_USER_REG where USER_OPENID = @USER_OPENID
end
END
GO
附上存储过程中用到的表结构
CREATE TABLE TBL_USER_REG_HIS(
[USER_ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CLIENT_ACCOUNT] varchar NULL,
[USER_OPENID] varchar NOT NULL,
[ACCOUNT_TYPE] varchar NOT NULL,
[ACCOUNT_NUM] varchar NOT NULL,
[ACCOUNT_PWD] varchar NULL,
[USER_MBLPHONE] varchar NOT NULL,
[IDCARD_NUM] varchar NOT NULL,
[USER_NAME] varchar NULL,
[dept_name] varchar NULL,
[CLIENT_MANAGER] varchar NULL,
[USER_GENDER] [int] NULL,
[USER_AGE] [int] NULL,
[DEPT_CODE] varchar NULL,
[CREATED_BY] [int] NOT NULL,
[CREATED_TIME] [datetime] NOT NULL,
[UPDATED_BY] [int] NOT NULL,
[UPDATED_TIME] [datetime] NOT NULL,
[CUSTOMER_ID] varchar NULL,
[REMARK] varchar NULL,
[tjr_code] varchar NULL,
[USER_SEX] varchar NULL,
[custjb] varchar NULL,
[custjbmc] varchar NULL
)
CREATE TABLE [dbo].[TBL_USER_REG](
[USER_ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CLIENT_ACCOUNT] [varchar](200) NULL,
[USER_OPENID] [varchar](200) NOT NULL,
[ACCOUNT_TYPE] [varchar](20) NOT NULL,
[ACCOUNT_NUM] [varchar](200) NOT NULL,
[ACCOUNT_PWD] [varchar](200) NULL,
[USER_MBLPHONE] [varchar](100) NOT NULL,
[IDCARD_NUM] [varchar](100) NOT NULL,
[USER_NAME] [varchar](50) NULL,
[dept_name] [varchar](200) NULL,
[CLIENT_MANAGER] [varchar](100) NULL,
[USER_GENDER] [int] NULL,
[USER_AGE] [int] NULL,
[DEPT_CODE] [varchar](100) NULL,
[CREATED_BY] [int] NOT NULL,
[CREATED_TIME] [datetime] NOT NULL,
[UPDATED_BY] [int] NOT NULL,
[UPDATED_TIME] [datetime] NOT NULL,
[CUSTOMER_ID] [varchar](100) NULL,
[remark] [varchar](500) NULL,
[tjr_code] [varchar](20) NULL,
[custjb] [varchar](8) NULL,
[custjbmc] [varchar](30) NULL,
[USER_SEX] [varchar](10) NULL
)
