Function to properly format a GP address

Some tasks you have to do often. I have to frequently write reports that have addresses on them (invoices, statements). This function is something that I carry from job to job to properly format the address.

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

 

-- =============================================
-- all address fields formatted for an invoice
-- =============================================
  
IF EXISTS (SELECT *
       FROM   sysobjects
       WHERE  name = N'ddf_Address')
    DROP FUNCTION ddf_Address
GO
  
  
CREATE FUNCTION ddf_Address
    (@name  varchar(31),
    @contact varchar(30),
    @address1 varchar(31),@address2 varchar(31),@address3 varchar(31),@city varchar(31) ,
    @state varchar(29),@zip varchar(11))
  
  
--select dbo.ddf_Address (@name, @contact,@address1 ,@address2 ,@address3 ,@city,@state ,@zip)
  
Returns varchar(200)
  
AS
begin
    declare @out varchar(200)
    set @out = ''
  
    if @name > ''
        set @out = rtrim(@name) + char(13) + char(10)
  
    if @contact > ''
        set @out = @out + rtrim(@contact) + char(13) + char(10)
  
    if @address1 > ''
        set @out = @out + rtrim(@address1) + ' '
    if @address2 > ''
        set @out = @out + rtrim(@address2) + ' '
    if @address3 > ''
        set @out = @out + rtrim(@address3)
  
    set @out = @out + char(13) + char(10)
  
    if @city > ''
        set @out = @out + rtrim(@city) + ', '
    if @state > ''
        set @out = @out + rtrim(@state) + ' '
    if @zip > ''
        set @out = @out + rtrim(@zip) + ' '
  
    return (@out)
end
go
grant exec on ddf_Address to public

 

 

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences