데이터 형식 varchar 및 varbinary이(가) add 연산자에서 호환되지 않습니다.;

오류

error code [402]; 데이터 형식 varchar 및 varbinary이(가) add 연산자에서 호환되지 않습니다.; 
nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 데이터 형식 varchar 및 varbinary이(가) add 연산자에서 호환되지 않습니다.

 

원인

파라미터 넘겨주는 곳에서 yn에 null 값이 들어가서 그렇다.

 

결론

MSSQL에서는 NULL이란 문자를 NULL로 인식해 ISNULL() 함수가 적용되면서 NULL이 아닌 대체할 문자('')로 반환을 하여 에러가 발생하지 않는다.

XML에서는 NULL을 인식하지 못하여 대체하는 문자('')가 아닌 NULL이라는 모양의 바이너리 데이터가 입력되어 에러가 발생하게 됩니다.

 

코드

<!-- 문서 파일에 있는 배부 목록 조회 -->
<select id="selectFileDistribution" parameterType="HashMap" resultType="DocInstituteDTO">
    WITH BIZ_CD_SEQ AS (
        SELECT
            DFILE.CORR_SEQ
            , DFILE.FILE_SEQ
            , FILE_ATTR_OBS.BIZ_CD_SEQ AS OBS
            , FILE_ATTR_FBS.BIZ_CD_SEQ AS FBS
        FROM
            DOM_DOC_FILE DFILE
            LEFT JOIN DOM_DOC_FILE_ATTR FILE_ATTR_OBS
                ON FILE_ATTR_OBS.CORR_SEQ = DFILE.CORR_SEQ
                AND FILE_ATTR_OBS.FILE_SEQ = DFILE.FILE_SEQ
                AND FILE_ATTR_OBS.BIZ_GRP_CD = 'OBS'
            LEFT JOIN DOM_DOC_FILE_ATTR FILE_ATTR_FBS
                ON FILE_ATTR_FBS.CORR_SEQ = DFILE.CORR_SEQ
                AND FILE_ATTR_FBS.FILE_SEQ = DFILE.FILE_SEQ
                AND FILE_ATTR_FBS.BIZ_GRP_CD = 'FBS'
        WHERE DFILE.CORR_SEQ = #{corrSeq}
        )
    SELECT
        #{corrSeq} AS CORR_SEQ
        , #{instTypeCd2} AS INST_TYPE_CD
        , DIST.OBS_BIZ_CD_SEQ AS BIZ_CD_SEQ
        , #{corrStatusCd2} AS RECEP_STATUS_CD
        , #{modiUserSeq} AS REG_USER_SEQ
        , #{delYn} AS DEL_YN
    FROM
        DOM_DOC_DISTRIBUTION DIST
        INNER JOIN BIZ_CD_SEQ
            ON BIZ_CD_SEQ.OBS = DIST.OBS_BIZ_CD_SEQ
            AND BIZ_CD_SEQ.FBS = DIST.FBS_BIZ_CD_SEQ
            AND (AGREE_YN LIKE '%' + #{yn} + '%'
                OR REVIEW_YN LIKE '%' + #{yn} + '%'
                OR REFER_YN LIKE '%' + #{yn} + '%')
</select>

WITH BIZ_CD_SEQ AS (
    SELECT
        DFILE.CORR_SEQ
        , DFILE.FILE_SEQ
        , FILE_ATTR_OBS.BIZ_CD_SEQ AS OBS
        , FILE_ATTR_FBS.BIZ_CD_SEQ AS FBS
    FROM
        DOM_DOC_FILE DFILE
        LEFT JOIN DOM_DOC_FILE_ATTR FILE_ATTR_OBS
            ON FILE_ATTR_OBS.CORR_SEQ = DFILE.CORR_SEQ
            AND FILE_ATTR_OBS.FILE_SEQ = DFILE.FILE_SEQ
            AND FILE_ATTR_OBS.BIZ_GRP_CD = 'OBS'
        LEFT JOIN DOM_DOC_FILE_ATTR FILE_ATTR_FBS
            ON FILE_ATTR_FBS.CORR_SEQ = DFILE.CORR_SEQ
            AND FILE_ATTR_FBS.FILE_SEQ = DFILE.FILE_SEQ
            AND FILE_ATTR_FBS.BIZ_GRP_CD = 'FBS'
    WHERE DFILE.CORR_SEQ = '67'
    )
SELECT
    '67' AS CORR_SEQ
    , 'RECEPTION' AS INST_TYPE_CD
    , DIST.OBS_BIZ_CD_SEQ AS BIZ_CD_SEQ
    , 'STANDBY' AS RECEP_STATUS_CD
    , 1 AS REG_USER_SEQ
    , 'N' AS DEL_YN
FROM
    DOM_DOC_DISTRIBUTION DIST
    INNER JOIN BIZ_CD_SEQ
        ON BIZ_CD_SEQ.OBS = DIST.OBS_BIZ_CD_SEQ
        AND BIZ_CD_SEQ.FBS = DIST.FBS_BIZ_CD_SEQ
        AND (AGREE_YN LIKE '%' + '1E' + '%'
            OR REVIEW_YN LIKE '%' + '1E' + '%'
            OR REFER_YN LIKE '%' + '1E' + '%')