Lexus.
2019-03-20 20:34
采纳率: 0%
浏览 1.9k
已结题

列名或所提供值的数目与表定义不匹配 sqlserver

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
)
  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

1条回答 默认 最新

  • threenewbee 2019-03-21 00:12

    INSERT INTO仔细检查下,列数量和下面的value还有数据库的不一致

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题