Technical Article

Returning values of combining nulls and non nulls

,

This script shows how to retrieve combination of different columns that may have nulls.

/*
This script shows how to retrieve combination of different columns that may have nulls.

tblADDRESS:

_ID  _NAMEADDR1        ADDR2 ADDR3CITYSTATEZIP
_____________________________________________________________________
10  AXXX CorpPO Box4x7NULL NULLPhoenixAZ85000
20  BXXX Corp80000 E.Plin DrSTE.850 NULLPhoenixAZ85000
30  CXXX Corpattn.M.Dept1 W.9St. Ste.1RoanokeVA24000
40  DXXX CorpNULL        NULL NULLNYNY11111


The procedure has to return full address line that combines values 
from ADDR1,ADDR2,ADDR3 or null if all of them have NULL.
*/

Create proc _getaddress_proc 

@id int,
@name varchar(30) OUTPUT,
@addr varchar(200) OUTPUT,
@city varchar(30) OUTPUT,
@ZIP varchar(12) OUTPUT
as


select  @name = _NAME, 
@addr = NULLIF(RTRIM( isnull(ADDR1,'')+' ' +isnull(ADDR2,'')+' '+isnull(ADDR3,'')),''),
@city = CITY,
@state = STATE,
@zip = ZIP
from    tblADDRESS
where _id = @id

return
/*
if you need to return an empty string instead of NULL - change @addr line to:
@addr = RTRIM(isnull(ADDR1,'')+' ' +isnull(ADDR2,'')+' '+isnull(ADDR3,''))
*/

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating