UFN_MAILPREFERENCE_VALIDATEUNIQUE
Returns true if the given preference does not duplicate the criteria of an existing preference.
Return
| Return Type |
|---|
| bit |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | IN | |
| @MAILTYPECODE | int | IN | |
| @BUSINESSUNITCODEID | uniqueidentifier | IN | |
| @CATEGORYCODEID | uniqueidentifier | IN | |
| @EVENTCATEGORYCODEID | uniqueidentifier | IN | |
| @SITEID | uniqueidentifier | IN | |
| @ACKNOWLEDGEMENTID | uniqueidentifier | IN | |
| @CORRESPONDENCEID | uniqueidentifier | IN | |
| @PLEDGEREMINDERID | uniqueidentifier | IN | |
| @CORRESPONDENCECODEID | uniqueidentifier | IN | |
| @PURPOSEID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_MAILPREFERENCE_VALIDATEUNIQUE
(
@ID uniqueidentifier,
@CONSTITUENTID uniqueidentifier,
@MAILTYPECODE int,
@BUSINESSUNITCODEID uniqueidentifier,
@CATEGORYCODEID uniqueidentifier,
@EVENTCATEGORYCODEID uniqueidentifier,
@SITEID uniqueidentifier,
@ACKNOWLEDGEMENTID uniqueidentifier,
@CORRESPONDENCEID uniqueidentifier,
@PLEDGEREMINDERID uniqueidentifier,
@CORRESPONDENCECODEID uniqueidentifier,
@PURPOSEID uniqueidentifier
)
returns bit
with execute as caller
as
begin
return case
when @MAILTYPECODE=0 then
( case
when not exists(
select ID
from dbo.MAILPREFERENCE
where
(ID <> @ID or @ID is null) and
CONSTITUENTID = @CONSTITUENTID and
((MAILTYPECODE = 0)) and
((ACKNOWLEDGEMENTID = @ACKNOWLEDGEMENTID) or (ACKNOWLEDGEMENTID is null and @ACKNOWLEDGEMENTID is null))
)
then 1
else 0 end
)
when @MAILTYPECODE=1 then
( case
when not exists(
select ID
from dbo.MAILPREFERENCE
where
(ID <> @ID or @ID is null) and
CONSTITUENTID = @CONSTITUENTID and
((MAILTYPECODE = 1)) and
((BUSINESSUNITCODEID = @BUSINESSUNITCODEID) or (BUSINESSUNITCODEID is null and @BUSINESSUNITCODEID is null)) and
((CATEGORYCODEID = @CATEGORYCODEID) or (CATEGORYCODEID is null and @CATEGORYCODEID is null)) and
((SITEID = @SITEID) or (SITEID is null and @SITEID is null))
)
then 1
else 0 end
)
when @MAILTYPECODE=2 then
( case
when not exists(
select ID
from dbo.MAILPREFERENCE
where
(ID <> @ID or @ID is null) and
CONSTITUENTID = @CONSTITUENTID and
((MAILTYPECODE = 2)) and
((EVENTCATEGORYCODEID = @EVENTCATEGORYCODEID) or (EVENTCATEGORYCODEID is null and @EVENTCATEGORYCODEID is null)) and
((SITEID = @SITEID) or (SITEID is null and @SITEID is null))
)
then 1
else 0 end
)
when @MAILTYPECODE=3 then
( case
when not exists(
select ID
from dbo.MAILPREFERENCE
where
(ID <> @ID or @ID is null) and
CONSTITUENTID = @CONSTITUENTID and
((MAILTYPECODE = 3)) and
((CORRESPONDENCEID = @CORRESPONDENCEID) or (CORRESPONDENCEID is null and @CORRESPONDENCEID is null)) and
((CORRESPONDENCECODEID = @CORRESPONDENCECODEID) or (CORRESPONDENCECODEID is null and @CORRESPONDENCECODEID is null))
)
then 1
else 0 end
)
when @MAILTYPECODE=4 then
( case
when not exists(
select ID
from dbo.MAILPREFERENCE
where
(ID <> @ID or @ID is null) and
CONSTITUENTID = @CONSTITUENTID and
((MAILTYPECODE = 4)) and
((PLEDGEREMINDERID = @PLEDGEREMINDERID) or (PLEDGEREMINDERID is null and @PLEDGEREMINDERID is null))
)
then 1
else 0 end
)
when @MAILTYPECODE=5 then
( case
when not exists(
select ID
from dbo.MAILPREFERENCE
where
(ID <> @ID or @ID is null) and
CONSTITUENTID = @CONSTITUENTID and
((MAILTYPECODE = 5))
)
then 1
else 0 end
)
when @MAILTYPECODE=6 then
( case
when not exists(
select ID
from dbo.MAILPREFERENCE
where
(ID <> @ID or @ID is null) and
CONSTITUENTID = @CONSTITUENTID and
((MAILTYPECODE = 6))
)
then 1
else 0 end
)
when @MAILTYPECODE=7 then
( case
when not exists(
select ID
from dbo.MAILPREFERENCE
where
(ID <> @ID or @ID is null) and
CONSTITUENTID = @CONSTITUENTID and
((MAILTYPECODE = 7))
)
then 1
else 0 end
)
when @MAILTYPECODE=8 then
( case
when not exists(
select ID
from dbo.MAILPREFERENCE
where
(ID <> @ID or @ID is null) and
CONSTITUENTID = @CONSTITUENTID and
((MAILTYPECODE = 8)) and
((PURPOSEID = @PURPOSEID) or (PURPOSEID is null and @PURPOSEID is null))
)
then 1
else 0 end
)
else
0
end
return 0
end