iVend Database Database : GetFullAddress User Defined Function

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@addressKey

In

BigInt

8

@RETURN_VALUE

Return Value

VarWChar

4000

Database Object

Object Type

Description

Dep Level

CfgAddress

Table

Defines the address details for each Address.

1

CfgCountry

Table

The country details are specified which can be selected further at the addresses entry of the customer/ vendor or which are related to the business in some way.

1

CfgState

Table

The state details are specified which can be selected further at the addresses entry of the customer/ vendor or which are related to the business in some way.

1

CREATE FUNCTION [dbo].[GetFullAddress]

(

@addressKey Bigint

)

RETURNS NVARCHAR(4000)

AS

BEGIN

DECLARE @fullAddress NVARCHAR(4000)

IF NOT EXISTS(SELECT * FROM CfgAddress WHERE AddressKey = @addressKey)

BEGIN

RETURN ''

END

SET @fullAddress = ''

SELECT @fullAddress = A.Address1

+ CASE ISNULL(A.Address2, '') WHEN '' THEN '' ELSE ', ' + A.Address2 END

+ CASE ISNULL(A.Address3, '') WHEN '' THEN '' ELSE ', ' + A.Address3 END

+ CASE ISNULL(A.City, '') WHEN '' THEN '' ELSE ', ' + A.City END

+ CASE ISNULL(B.Description, '') WHEN '' THEN '' ELSE ', ' + B.Description END

+ CASE ISNULL(C.Description, '') WHEN '' THEN '' ELSE ', ' + C.Description END

FROM CfgAddress A

LEFT OUTER JOIN CfgState B ON A.StateKey = B.StateKey

LEFT OUTER JOIN CfgCountry C ON A.CountryKey = C.CountryKey

WHERE A.AddressKey = @addressKey

RETURN @fullAddress;

END