JanetStackpole
Programmer
Hello,
I am getting the following error when executing a stored procedure in MS SQL 2005. This procedure executes correctly in 2000. Any assistance would be greatly appreciated. I have included the SP
error message
Msg 8115, Level 16, State 6, Procedure CMP_11_ADD_BH_ADMIT, Line 35
Arithmetic overflow error converting varchar to data type numeric.
I am getting the following error when executing a stored procedure in MS SQL 2005. This procedure executes correctly in 2000. Any assistance would be greatly appreciated. I have included the SP
error message
Msg 8115, Level 16, State 6, Procedure CMP_11_ADD_BH_ADMIT, Line 35
Arithmetic overflow error converting varchar to data type numeric.
Code:
CREATE PROCEDURE [dbo].[CMP_11_ADD_BH_ADMIT]
AS
/* DECLARE AND SET DATA SYSTEM AND EXTRACT FILE NAME VARIABLES */
DECLARE
@DataSystemName AS VARCHAR(30),
@ExtractFileName AS VARCHAR (30)
SET @DataSystemName = 'BH'
SET @ExtractFileName = 'BH'
TRUNCATE TABLE ABD_MI50_User.dbo.ABD_BH_ADMIT
INSERT INTO ABD_MI50_User.dbo.ABD_BH_ADMIT
(
PAT_ID, ATT_PROV, ADM_DATE, END_DATE, DIS_STAT, ICD9_DIAG_01, ICD9_DIAG_02, ABD_RECTYPE, ABD_LEVEL_OF_CARE,
....... )
SELECT
/*R*/ --PAT_ID,
UCI,
/*R*/ --ATT_PROV,
RIGHT('000000000000' + LTRIM(RTRIM(UPID)),12),
--ADM_DATE,
CASE
WHEN LEN(ADMDATE) = 0 THEN NULL
WHEN ISDATE(ADMDATE) = 1 THEN CAST(ADMDATE AS DATETIME)
ELSE NULL
END,
--END_DATE,
CASE
WHEN LEN(LASTDTSVC) = 0 THEN NULL
WHEN ISDATE(LASTDTSVC) = 1 THEN CAST(LASTDTSVC AS DATETIME)
ELSE NULL
END,
--DIS_STAT,
CASE
WHEN LEN(LTRIM(DISDSCHG)) = 0 THEN NULL
WHEN ISDATE(DISDSCHG) = 1 THEN CAST(DISDSCHG AS DATETIME)
ELSE NULL
END,
--ICD9_DIAG_01,
CASE
WHEN LEN(LTRIM(PRIMDX)) = 0 THEN NULL
ELSE PRIMDX
END,
--ICD9_DIAG_02,
CASE
WHEN LEN(LTRIM(SECONDX)) = 0 THEN NULL
ELSE SECONDX
END,
-- CLIENT SPECIFIC FIELDS:
--ABD_RECTYPE,
CASE
WHEN LEN(LTRIM(RECTYPE)) = 0 THEN NULL
ELSE RECTYPE
END,
--ABD_LEVEL_OF_CARE,
CASE
WHEN LEN(LTRIM(LOCAOD)) = 0 THEN NULL
ELSE LOCAOD
END,
--ABD_CLOSURE_DT,
CASE
WHEN LEN(CLOSDATE) = 0 THEN NULL
WHEN ISDATE(CLOSDATE) = 1 THEN CAST(CLOSDATE AS DATETIME)
ELSE NULL
END,
--ABD_CODEPEND,
CASE
WHEN LEN(LTRIM(CODEPEND)) = 0 THEN NULL
ELSE CODEPEND
END,
--ABD_PATCTRLNO,
CASE
WHEN LEN(LTRIM(PATCTRLNO)) = 0 THEN NULL
ELSE PATCTRLNO
END,
--ABD_REFBYCODE,
CASE
WHEN LEN(LTRIM(REFBYCODE)) = 0 THEN NULL
ELSE REFBYCODE
END,
--ABD_REFBY_PROV,
CASE
WHEN LEN(LTRIM(RTRIM(REFBYUPID))) = 0 THEN NULL
ELSE RIGHT('000000000000' + LTRIM(RTRIM(REFBYUPID)),12)
END,
--ABD_EDU_LEVEL,
CASE
WHEN LEN(LTRIM(EDLEVEL)) = 0 THEN NULL
ELSE EDLEVEL
END,
--ABD_EDU_TYPE,
CASE
WHEN LEN(LTRIM(EDTYPE)) = 0 THEN NULL
ELSE EDTYPE
END,
--ABD_EMP_STATUS,
CASE
WHEN LEN(LTRIM(EMPSTATUS)) = 0 THEN NULL
ELSE EMPSTATUS
END,
--ABD_INCSOURCE,
CASE
WHEN LEN(LTRIM(INCSOURCE)) = 0 THEN NULL
ELSE INCSOURCE
END,
--ABD_LIVING_ARR,
CASE
WHEN LEN(LTRIM(LIIVINGARR)) = 0 THEN NULL
ELSE LIIVINGARR
END,
--ABD_IPH_EPI,
CASE
WHEN LEN(LTRIM(RTRIM(INPATEPI))) = 0 THEN NULL
WHEN ISNUMERIC(INPATEPI) = 1 THEN CAST(INPATEPI AS NUMERIC)
ELSE NULL
END,
--ABD_INT_OPH_EPI,
CASE
WHEN LEN(LTRIM(RTRIM(IOPEPI))) = 0 THEN NULL
WHEN ISNUMERIC(IOPEPI) = 1 THEN CAST(IOPEPI AS NUMERIC)
ELSE NULL
END,
--ABD_GEN_OPH_EPI,
CASE
WHEN LEN(LTRIM(RTRIM(OPEPI))) = 0 THEN NULL
WHEN ISNUMERIC(OPEPI) = 1 THEN CAST(OPEPI AS NUMERIC)
ELSE NULL
END,
--ABD_REHAB_EPI,
CASE
WHEN LEN(LTRIM(RTRIM(REHABEPI))) = 0 THEN NULL
WHEN ISNUMERIC(REHABEPI) = 1 THEN CAST(REHABEPI AS NUMERIC)
ELSE NULL
END,
--ABD_DX_TYPE,
CASE
WHEN LEN(LTRIM(RTRIM(DXTYPE))) = 0 THEN NULL
ELSE DXTYPE
END,
--ABD_METHADONE,
CASE
WHEN LEN(LTRIM(RTRIM(METHADONE))) = 0 THEN NULL
ELSE METHADONE
END,
--ABD_NUM_CHILD,
CASE
WHEN LEN(LTRIM(RTRIM(NOCHILD))) = 0 THEN NULL
WHEN ISNUMERIC(NOCHILD) = 1 THEN CAST(NOCHILD AS NUMERIC)
ELSE NULL
END,
--ABD_SMDSE,
CASE
WHEN LEN(LTRIM(RTRIM(SMDSED))) = 0 THEN NULL
ELSE SMDSED
END,
--ABD_AODABUSE,
CASE
WHEN LEN(LTRIM(RTRIM(AODABUSE))) = 0 THEN NULL
ELSE AODABUSE
END,
--ABD_FORENSIC,
CASE
WHEN LEN(LTRIM(RTRIM(FORENSIC))) = 0 THEN NULL
ELSE FORENSIC
END,
--ABD_DD,
CASE
WHEN LEN(LTRIM(RTRIM(DD))) = 0 THEN NULL
ELSE DD
END,
--ABD_MIMR_DUAL,
CASE
WHEN LEN(LTRIM(RTRIM(MIMRDUAL))) = 0 THEN NULL
ELSE MIMRDUAL
END,
--ABD_DUI,
CASE
WHEN LEN(LTRIM(RTRIM(DUIDWI))) = 0 THEN NULL
ELSE DUIDWI
END,
--ABD_DEAF,
CASE
WHEN LEN(LTRIM(RTRIM(DEAF))) = 0 THEN NULL
ELSE DEAF
END,
--ABD_HEAR_IMP,
CASE
WHEN LEN(LTRIM(RTRIM(HEARIMP))) = 0 THEN NULL
ELSE HEARIMP
END,
--ABD_BLIND,
CASE
WHEN LEN(LTRIM(RTRIM(BLIND))) = 0 THEN NULL
ELSE BLIND
END,
--ABD_VIS_IMP,
CASE
WHEN LEN(LTRIM(RTRIM(VISIMP))) = 0 THEN NULL
ELSE VISIMP
END,
--ABD_PHYS_DISAB,
CASE
WHEN LEN(LTRIM(RTRIM(PHYSDISAB))) = 0 THEN NULL
ELSE PHYSDISAB
END,
--ABD_SPEECH_IMP,
CASE
WHEN LEN(LTRIM(RTRIM(SPEECHIMP))) = 0 THEN NULL
ELSE SPEECHIMP
END,
--ABD_PHYSABU_VIC,
CASE
WHEN LEN(LTRIM(RTRIM(PHYSABUVIC))) = 0 THEN NULL
ELSE PHYSABUVIC
END,
--ABD_SEXABU_VIC,
CASE
WHEN LEN(LTRIM(RTRIM(SEXABUVIC))) = 0 THEN NULL
ELSE SEXABUVIC
END,
--ABD_DOMVIOL,
CASE
WHEN LEN(LTRIM(RTRIM(DOMVIOL))) = 0 THEN NULL
ELSE DOMVIOL
END,
--ABD_CHILD_AOD,
CASE
WHEN LEN(LTRIM(RTRIM(CHILDAODAB))) = 0 THEN NULL
ELSE CHILDAODAB
END,
--ABD_HIVAIDS,
CASE
WHEN LEN(LTRIM(RTRIM(HIVAIDS))) = 0 THEN NULL
ELSE HIVAIDS
END,
--ABD_SUICIDAL,
CASE
WHEN LEN(LTRIM(RTRIM(SUICIDAL))) = 0 THEN NULL
ELSE SUICIDAL
END,
--ABD_SCHOOL_DO,
CASE
WHEN LEN(LTRIM(RTRIM(SCHOOLDO))) = 0 THEN NULL
ELSE SCHOOLDO
END,
--ABD_PROBPAROLE,
CASE
WHEN LEN(LTRIM(RTRIM(PROBPAROLE))) = 0 THEN NULL
ELSE PROBPAROLE
END,
--ABD_NONE,
CASE
WHEN LEN(LTRIM(RTRIM(NONE))) = 0 THEN NULL
ELSE NONE
END,
--ABD_PREG,
CASE
WHEN LEN(LTRIM(RTRIM(PREG))) = 0 THEN NULL
ELSE PREG
END,
--ABD_PREMED_HMO,
CASE
WHEN LEN(LTRIM(RTRIM(PREMEDHMO))) = 0 THEN NULL
ELSE PREMEDHMO
END,
--ABD_HAS_PCP,
CASE
WHEN LEN(LTRIM(RTRIM(PCPHY))) = 0 THEN NULL
ELSE PCPHY
END,
--ABD_REF_ASSESS,
CASE
WHEN LEN(LTRIM(RTRIM(REFASSESS))) = 0 THEN NULL
ELSE REFASSESS
END,
--ABD_BIRTH_TYPE,
CASE
WHEN LEN(LTRIM(RTRIM(BIRTHTYPE))) = 0 THEN NULL
ELSE BIRTHTYPE
END,
--ABD_URINALYSIS,
CASE
WHEN LEN(LTRIM(RTRIM(URINALYSIS))) = 0 THEN NULL
ELSE URINALYSIS
END,
--ABD_MH_HIS,
CASE
WHEN LEN(LTRIM(RTRIM(MHHX))) = 0 THEN NULL
ELSE MHHX
END,
--ABD_INTOX_AGE,
CASE
WHEN LEN(LTRIM(RTRIM(AGEFIRALCO))) = 0 THEN NULL
WHEN ISNUMERIC(AGEFIRALCO) = 1 THEN CAST(AGEFIRALCO AS NUMERIC)
ELSE NULL
END,
--ABD_NO_ARREST,
CASE
WHEN LEN(LTRIM(RTRIM(NOARREST))) = 0 THEN NULL
WHEN ISNUMERIC(NOARREST) = 1 THEN CAST(NOARREST AS NUMERIC)
ELSE NULL
END,
--ABD_PRIM_DRUG_CD,
CASE
WHEN LEN(LTRIM(RTRIM(PRIMDRUG))) = 0 THEN NULL
ELSE PRIMDRUG
END,
--ABD_PRIM_FREQ,
CASE
WHEN LEN(LTRIM(RTRIM(PRIMFREQ))) = 0 THEN NULL
WHEN ISNUMERIC(PRIMFREQ) = 1 THEN CAST(PRIMFREQ AS NUMERIC)
ELSE NULL
END,
--ABD_PRIM_ROUTE,
CASE
WHEN LEN(LTRIM(RTRIM(PRIMROUTE))) = 0 THEN NULL
ELSE PRIMROUTE
END,
--ABD_PRIM_FIRST_YR,
CASE
WHEN LEN(LTRIM(RTRIM(FIRSTYRUSE))) = 0 THEN NULL
ELSE FIRSTYRUSE
END,
--ABD_SEC_DRUG_CD,
CASE
WHEN LEN(LTRIM(RTRIM(SECDRUG))) = 0 THEN NULL
ELSE SECDRUG
END,
--ABD_SEC_FREQ,
CASE
WHEN LEN(LTRIM(RTRIM(SECFREQ))) = 0 THEN NULL
WHEN ISNUMERIC(SECFREQ) = 1 THEN CAST(SECFREQ AS NUMERIC)
ELSE NULL
END,
--ABD_SEC_ROUTE,
CASE
WHEN LEN(LTRIM(RTRIM(SECROUTE))) = 0 THEN NULL
ELSE SECROUTE
END,
--ABD_SEC_FIRST_YR,
CASE
WHEN LEN(LTRIM(RTRIM(SECYRUSE))) = 0 THEN NULL
ELSE SECYRUSE
END,
--ABD_TER_DRUG_CD,
CASE
WHEN LEN(LTRIM(RTRIM(TERDRUG))) = 0 THEN NULL
ELSE TERDRUG
END,
--ABD_TER_FREQ,
CASE
WHEN LEN(LTRIM(RTRIM(TERFREQ))) = 0 THEN NULL
WHEN ISNUMERIC(TERFREQ) = 1 THEN CAST(TERFREQ AS NUMERIC)
ELSE NULL
END,
--ABD_TER_ROUTE,
CASE
WHEN LEN(LTRIM(RTRIM(TERROUTE))) = 0 THEN NULL
ELSE TERROUTE
END,
--ABD_TER_FIRST_YR,
CASE
WHEN LEN(LTRIM(RTRIM(TERYRUSE))) = 0 THEN NULL
ELSE TERYRUSE
END,
--ABD_HOSPADM,
CASE
WHEN LEN(LTRIM(RTRIM(HOSPADM))) = 0 THEN NULL
WHEN ISNUMERIC(HOSPADM) = 1 THEN HOSPADM
ELSE NULL
END,
--ABD_ERADM,
CASE
WHEN LEN(LTRIM(RTRIM(ERADM))) = 0 THEN NULL
WHEN ISNUMERIC(ERADM) = 1 THEN ERADM
ELSE NULL
END,
--ABD_REG_TO_PHY,
CASE
WHEN LEN(LTRIM(RTRIM(REGTOPHY))) = 0 THEN NULL
WHEN ISNUMERIC(REGTOPHY) = 1 THEN REGTOPHY
ELSE NULL
END,
--ABD_REG_TO_DEN,
CASE
WHEN LEN(LTRIM(RTRIM(REGTODEN))) = 0 THEN NULL
WHEN ISNUMERIC(REGTODEN) = 1 THEN REGTODEN
ELSE NULL
END,
--ABD_OPHC_VIS,
CASE
WHEN LEN(LTRIM(RTRIM(OPHCVIS))) = 0 THEN NULL
WHEN ISNUMERIC(OPHCVIS) = 1 THEN OPHCVIS
ELSE NULL
END,
--ABD_REIMB_SELF,
CASE
WHEN LEN(LTRIM(RTRIM(REIMSELF))) = 0 THEN NULL
ELSE REIMSELF
END,
--ABD_REIMB_BCBS,
CASE
WHEN LEN(LTRIM(RTRIM(REIMBCBS))) = 0 THEN NULL
ELSE REIMBCBS
END,
--ABD_REIMB_PRIV,
CASE
WHEN LEN(LTRIM(RTRIM(REIMBPRIV))) = 0 THEN NULL
ELSE REIMBPRIV
END,
--ABD_REIMB_HMO,
CASE
WHEN LEN(LTRIM(RTRIM(REIMBHMO))) = 0 THEN NULL
ELSE REIMBHMO
END,
--ABD_REIMB_OTH_PRIV,
CASE
WHEN LEN(LTRIM(RTRIM(REBOTHPRIV))) = 0 THEN NULL
ELSE REBOTHPRIV
END,
--ABD_REIMB_COURT,
CASE
WHEN LEN(LTRIM(RTRIM(REIMCOURT))) = 0 THEN NULL
ELSE REIMCOURT
END,
--ABD_REIMB_WORK,
CASE
WHEN LEN(LTRIM(RTRIM(REIMWORK))) = 0 THEN NULL
ELSE REIMWORK
END,
--ABD_REIMB_REHAB,
CASE
WHEN LEN(LTRIM(RTRIM(REIMREHAB))) = 0 THEN NULL
ELSE REIMREHAB
END,
--ABD_REIMB_EMP,
CASE
WHEN LEN(LTRIM(RTRIM(REIMBEMP))) = 0 THEN NULL
ELSE REIMBEMP
END,
--ABD_MCR,
CASE
WHEN LEN(LTRIM(RTRIM(MEDICARE))) = 0 THEN NULL
ELSE MEDICARE
END,
--ABD_MCD,
CASE
WHEN LEN(LTRIM(RTRIM(MEDICAID))) = 0 THEN NULL
ELSE MEDICAID
END,
--ABD_TITLEXX,
CASE
WHEN LEN(LTRIM(RTRIM(TITLEXX))) = 0 THEN NULL
ELSE TITLEXX
END,
--ABD_REIMB_FOREN,
CASE
WHEN LEN(LTRIM(RTRIM(REIMFOREN))) = 0 THEN NULL
ELSE REIMFOREN
END,
--ABD_REIMB_OTHGOV,
CASE
WHEN LEN(LTRIM(RTRIM(REIMOTHGOV))) = 0 THEN NULL
ELSE REIMOTHGOV
END,
--ABD_REIMB_OTHPAY,
CASE
WHEN LEN(LTRIM(RTRIM(REIMOTHPAY))) = 0 THEN NULL
ELSE REIMOTHPAY
END,
--ABD_NOCHARGE,
CASE
WHEN LEN(LTRIM(RTRIM(NOCHARGE))) = 0 THEN NULL
ELSE NOCHARGE
END,
--ABD_INSEXHAUS,
CASE
WHEN LEN(LTRIM(RTRIM(INSEXHAUS))) = 0 THEN NULL
ELSE INSEXHAUS
END,
--ABD_DISPUTED,
CASE
WHEN LEN(LTRIM(RTRIM(DISPUTED))) = 0 THEN NULL
ELSE DISPUTED
END,
--ABD_NONCOOP,
CASE
WHEN LEN(LTRIM(RTRIM(NONCOOP))) = 0 THEN NULL
ELSE NONCOOP
END,
--ABD_NORESINSCO,
CASE
WHEN LEN(LTRIM(RTRIM(NORESINSCO))) = 0 THEN NULL
ELSE NORESINSCO
END,
--ABD_NOTCOV,
CASE
WHEN LEN(LTRIM(RTRIM(NOTCOV))) = 0 THEN NULL
ELSE NOTCOV
END,
--ABD_BDCOV,
CASE
WHEN LEN(LTRIM(RTRIM(BDCOV))) = 0 THEN NULL
ELSE BDCOV
END,
--ABD_DOS_REQ,
CASE
WHEN LEN(DOSREQ) = 0 THEN NULL
WHEN ISDATE(DOSREQ) = 1 THEN CAST(DOSREQ AS DATETIME)
ELSE NULL
END,
--ABD_VET_STAT,
CASE
WHEN LEN(LTRIM(RTRIM(VETSTAT))) = 0 THEN NULL
ELSE VETSTAT
END,
--ABD_CHILD_CUST,
CASE
WHEN LEN(LTRIM(RTRIM(CHILDCUS))) = 0 THEN NULL
ELSE CHILDCUS
END,
--ABD_LEGAL_STAT,
CASE
WHEN LEN(LTRIM(RTRIM(LEGALSTAT))) = 0 THEN NULL
ELSE LEGALSTAT
END,
--ABD_SALARY,
CASE
WHEN LEN(INDINCOME) = 0 THEN NULL
WHEN ISNUMERIC(INDINCOME) = 1 THEN CAST(INDINCOME AS NUMERIC)
ELSE NULL
END,
--ABD_REFTOCD,
CASE
WHEN LEN(LTRIM(RTRIM(REFTOCODE))) = 0 THEN NULL
ELSE REFTOCODE
END,
--ABD_REFTO_PROV,
CASE
WHEN LEN(LTRIM(RTRIM(REFTOUPID))) = 0 THEN NULL
ELSE RIGHT('000000000000' + LTRIM(RTRIM(REFTOUPID)),12)
END,
--ABD_DT_SENT,
CASE
WHEN LEN(DTPROVSENT) = 0 THEN NULL
WHEN ISDATE(DTPROVSENT) = 1 THEN CAST(DTPROVSENT AS DATETIME)
ELSE NULL
END,
/*R*/ --ABD_BH_DATA_SYS,
@DataSystemName,
/*R*/ --ABD_BH_DATA_SRC,
@ExtractFileName,
/*S*/ --ABD_BH_INS
GETDATE()
/*S*/ --ABD_BH_UPD
FROM ABD_MI50_Source.dbo.STATE_BH
WHERE RECTYPE='A'