USP_BBNC_GETADDRESSBYCONSTITID
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ConstituentID | uniqueidentifier | IN | |
| @DONOTMAIL | bit | INOUT | |
| @DONOTMAILREASONCODEID | uniqueidentifier | INOUT | |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | |
| @COUNTRYID | uniqueidentifier | IN | |
| @ADDRESSBLOCK | nvarchar(150) | IN | |
| @STATEID | uniqueidentifier | IN | |
| @CITY | nvarchar(50) | IN | |
| @POSTCODE | nvarchar(12) | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_GETADDRESSBYCONSTITID(
@ConstituentID uniqueidentifier,
@DONOTMAIL bit output,
@DONOTMAILREASONCODEID uniqueidentifier output,
@ADDRESSTYPECODEID uniqueidentifier = null,
@COUNTRYID uniqueidentifier = null,
@ADDRESSBLOCK nvarchar(150) = null,
@STATEID uniqueidentifier = null,
@CITY nvarchar(50) = null,
@POSTCODE nvarchar(12) = null)
as
select @DONOTMAIL = Address.DONOTMAIL,@DONOTMAILREASONCODEID = Address.DONOTMAILREASONCODEID from Address
inner join CONSTITUENT C on C.ID = Address.CONSTITUENTID where C.ID= @ConstituentID and (Address.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or (Address.ADDRESSTYPECODEID is null and @ADDRESSTYPECODEID is null))
and (Address.COUNTRYID = @COUNTRYID or (@COUNTRYID is null and Address.COUNTRYID is null)) and (Address.ADDRESSBLOCK = @ADDRESSBLOCK or (Address.ADDRESSBLOCK is null and @ADDRESSBLOCK is null))
and (Address.STATEID = @STATEID or (Address.STATEID is null and @STATEID is null))
and Address.CITY = isnull(@CITY,CITY) and Address.POSTCODE = isnull(@POSTCODE,POSTCODE);