Let me explain a bit better what I need to do and then post my whole code.

I need to write a script, where i can insert a name id, and the it will return the adr1, adr2,adr3 postal code and country.

If any of those are null, it should not select that column i.e. if adr3 is null, it should only return the other columns. If adr2 and adr3 is null, it should select all the other columns except those to.

Here is all me code:

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

SET @NAMEID = '14388'

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'