@casenotedesc [varchar](2000), @cdstatus [tinyint], @checkdiagnose [varchar](20), @jcxm [varchar](100) ) AS DECLARE @PatientCount as int SELECT @PatientCount =Count(*) FROM Patient WHERE [INSTRUMENTID]= CONVERT(tinyint,@instrumentid) AND [ENDSCPID]=CONVERT(int,@endscpid)if @PatientCount=0 Begin INSERT INTO [Patient] ( MEDICCAREID, INHOSPITAL, SICKBEDID, ENDSCPID, ENDSCPCODE, INSTRUMENTID, PATIENTNAME, SEX, BIRTHDAY, AGE,
[CASENOTEDESC]= @casenotedesc, [CDSTATUS]= @cdstatus, [CHECKDIAGNOSE]=@checkdiagnose, [JCXM]=@jcxm WHERE [INSTRUMENTID]= CONVERT(tinyint,@instrumentid) AND [ENDSCPID]=CONVERT(int,@endscpid) END
一边删除一边测试
或者用
return
不是你的表中有NVARCHAR类型的,是你调用存储过程时输入的值是NVARCHAR类型的,到过程里面插入表时转化为smallint时出错了。主要检查一下给你表里smallint字段赋值时有没有字符串的。
比如你表里面CHECKDR smallint是smallint类型的,传的值@checkdr就是NVARCHAR的
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[add_patientinfo]
(
@mediccareid [varchar](20),
@inhospital [varchar](20),
@sickbedid [varchar](10),
@endscpid [varchar](9),
@endscpcode [smallint], @instrumentid [varchar](2),
@patientname [varchar](10),
@sex [char](1),
@birthday [datetime],
@age [varchar](4), @phone [varchar](30),
@address [varchar](40),
@worksite [varchar](40),
@askhospt [smallint],
@askdept [smallint], @askdr [smallint],
@asktime [datetime],
@checkdr [smallint],
@checkdept [smallint],
@checktime [datetime],
@reportdr [smallint],
@reportdept [smallint],
@reporttime [datetime],
@imagecount [smallint],
@videocount [smallint],
@checkstatus [tinyint],
@checkdesc [varchar](3000),
@diagnosedesc [varchar](2000),
@recordcdno [varchar](20),
@sampcount [tinyint],
@casenotedesc [varchar](2000),
@cdstatus [tinyint],
@checkdiagnose [varchar](20),
@jcxm [varchar](100)
)
AS
DECLARE @PatientCount as int
SELECT @PatientCount =Count(*) FROM Patient
WHERE
[INSTRUMENTID]= CONVERT(tinyint,@instrumentid)
AND
[ENDSCPID]=CONVERT(int,@endscpid)if @PatientCount=0
Begin
INSERT INTO [Patient]
(
MEDICCAREID,
INHOSPITAL,
SICKBEDID,
ENDSCPID,
ENDSCPCODE, INSTRUMENTID,
PATIENTNAME,
SEX,
BIRTHDAY,
AGE,
PHONE,
ADDRESS,
WORKSITE,
ASKHOSPT,
ASKDEPT, ASKDR,
ASKTIME,
CHECKDR,
CHECKDEPT,
CHECKTIME, REPORTDR,
REPORTDEPT,
REPORTTIME,
IMAGECOUNT,
VIDEOCOUNT, CHECKSTATUS,
CHECKDESC,
DIAGNOSEDESC,
RECORDCDNO,
SAMPCOUNT,
CASENOTEDESC,
CDSTATUS,
CHECKDIAGNOSE,
JCXM
)
VALUES
(
@mediccareid,
@inhospital,
@sickbedid,
CONVERT(int,@endscpid),
@endscpcode,
CONVERT(tinyint,@instrumentid),
@patientname,
@sex,
@birthday,
CONVERT(smallint,@age),
@phone,
@address,
@worksite,
@askhospt,
@askdept,
@askdr,
@asktime,
@checkdr,
@checkdept,
@checktime,
@reportdr,
@reportdept,
@reporttime,
@imagecount,
@videocount,
@checkstatus,
@checkdesc,
@diagnosedesc,
@recordcdno,
@sampcount,
@casenotedesc,
@cdstatus,
@checkdiagnose,
@jcxm
)
end
ELSE
BEGIN
UPDATE [Patient]
SET
[MEDICCAREID]= @mediccareid,
[INHOSPITAL]=@inhospital,
[SICKBEDID]=@sickbedid,
[ENDSCPID]=CONVERT(int,@endscpid),
[ENDSCPCODE]=@endscpcode,
[INSTRUMENTID]= CONVERT(tinyint,@instrumentid),
[PATIENTNAME]=@patientname,
[SEX]=@sex,
[BIRTHDAY]= @birthday,
[AGE]=CONVERT(smallint,@age),
[PHONE]=@phone,
[ADDRESS]=@address,
[WORKSITE]=@worksite,
[ASKHOSPT]=@askhospt,
[ASKDEPT]=@askdept,
[ASKDR]= @askdr,
[ASKTIME]=@asktime,
[CHECKDR]=@checkdr,
[CHECKDEPT]=@checkdept,
[CHECKTIME]=@checktime,
[REPORTDR]=@reportdr,
[REPORTDEPT]=@reportdept,
[REPORTTIME]=@reporttime,
[IMAGECOUNT]= @imagecount,
[VIDEOCOUNT]=@videocount,
[CHECKSTATUS]=@checkstatus,
[CHECKDESC]= @checkdesc,
[DIAGNOSEDESC]= @diagnosedesc,
[RECORDCDNO]=@recordcdno,
[SAMPCOUNT]=@sampcount,
[CASENOTEDESC]= @casenotedesc,
[CDSTATUS]= @cdstatus,
[CHECKDIAGNOSE]=@checkdiagnose,
[JCXM]=@jcxm
WHERE
[INSTRUMENTID]= CONVERT(tinyint,@instrumentid)
AND
[ENDSCPID]=CONVERT(int,@endscpid)
END