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,'')) */
A short but interesting article, the author has figured out a way to create a PDF from a stored procedure without using a third party library.
2019-09-20 (first published: 2003-08-26)
73,133 reads
Robert is back this week with a look at the text data type. Not the easiest thing to work with, but sometimes a varchar just doesn't give you the range you need.
2003-05-07
27,341 reads
Fast Track to MDX gives readers all the necessary background information needed to write useful, powerful MDX expressions and introduces the most frequently used MDX functions and constructs. No prior knowledge is assumed and examples are used throughout the book to rapidly develop MDX skills to the point where a reader can solve real business problems. A CD containing examples from within the book, and a time-limited version of ProClarity, is included
2003-05-01
2,698 reads
Robert is our expert on dynamic sql. This week he offers some good hints for planning the contruction of a proc that will use dynamic sql. He also adds some suggestions on how to format the code so that when you return to it later, you can figure out what you were doing!
2007-10-02 (first published: 2003-04-25)
50,561 reads
In this article Andy looks at one way you can separate your presentation code from your data when you generate HTML tables for simple reports. Lots of code examples so you can see how it works.
2002-08-05
5,909 reads
