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  _NAME	ADDR1	        ADDR2	 ADDR3	CITY	STATE	ZIP
_____________________________________________________________________
10  AXXX Corp	PO Box4x7	NULL	 NULL	Phoenix	AZ	85000
20  BXXX Corp	80000 E.Plin Dr	STE.850	 NULL	Phoenix	AZ	85000
30  CXXX Corp	attn.M.Dept	1 W.9St. Ste.1	Roanoke	VA	24000
40  DXXX Corp	NULL	        NULL	 NULL	NY	NY	11111


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

Share

Share

Rate