ah I see.

This should be easier than you think.

DECLARE @NAMEID int, @ADR2 VARCHAR(100), @ADR3 VARCHAR(100)

SET @NAMEID = '14388'

--just add this line

SELECT @ADR2 = ADR2, @ADR3 = ADR3 FROM PAT_NAMES_ADDRESS WHERE NAME_ID = @NAMEID

IF @ADR2 IS NULL AND @ADR3 IS NULL

SELECT ADR1, POSTAL_CODE, (STATE_NAME) AS COUNTRY

FROM PAT_NAMES_ADDRESS P, STATE_NAME S

WHERE P.NAME_ID = @nameid

AND P.STATE_ID = S.STATE_ID

AND P.CURRENT_ONE = '1'

AND S.LANGUAGE_ID = '3'

ELSE IF @ADR3 IS NULL AND @ADR2 IS NOT NULL

SELECT ADR1,ADR2, POSTAL_CODE, (STATE_NAME) AS COUNTRY

FROM PAT_NAMES_ADDRESS P, STATE_NAME S

WHERE P.NAME_ID = @nameid

AND P.STATE_ID = S.STATE_ID

AND P.CURRENT_ONE = '1'

AND S.LANGUAGE_ID = '3'

IF @ADR2 IS NULL AND @ADR3 IS NOT NULL

SELECT ADR1,ADR3, POSTAL_CODE, (STATE_NAME) AS COUNTRY

FROM PAT_NAMES_ADDRESS P, STATE_NAME S

WHERE P.NAME_ID = @nameid

AND P.STATE_ID = S.STATE_ID

AND P.CURRENT_ONE = '1'

AND S.LANGUAGE_ID = '3'

ELSE IF @ADR2 IS NOT NULL AND @ADR3 IS NOT NULL

SELECT ADR1, ADR2, ADR3, POSTAL_CODE, (STATE_NAME) AS COUNTRY

FROM PAT_NAMES_ADDRESS P, STATE_NAME S

WHERE P.NAME_ID = @nameid

AND P.STATE_ID = S.STATE_ID

AND P.CURRENT_ONE = '1'

AND S.LANGUAGE_ID = '3'