USP_CREATEORUPDATE_BUILDNAMEFORMATTVF
This functions creates or updates the table value function used to represent a name format.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_CREATEORUPDATE_BUILDNAMEFORMATTVF
(
@ID uniqueidentifier, -- NAMEFORMATFUNCTION.ID,
@CHANGEAGENTID uniqueidentifier = null
)
with execute as caller
as
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()
begin try
declare @SQL nvarchar(max);
declare @SQL_SELECT nvarchar(max) = '';
declare @SQL_FROM nvarchar(max);
declare @SPOUSETABLE_INCLUDED bit = 0; -- used for spouse information, typecode: 6,7,8,9,10
declare @NAMEFORMAT_TVF nvarchar(200) = 'UFN_NAMEFORMAT_' + replace(cast(@ID as nvarchar(36)), '-', '_') + '_TVF';
declare @CREATEORALTER nvarchar(7);
if exists(select object_id from sys.objects where type = 'IF' and name = @NAMEFORMAT_TVF)
set @CREATEORALTER = 'alter ';
else
set @CREATEORALTER = 'create ';
set @SQL = @CREATEORALTER + 'function dbo.' + @NAMEFORMAT_TVF + '(@CONSTITUENTID uniqueidentifier)' + char(10);
set @SQL = @SQL + 'returns table' + char(10);
set @SQL = @SQL + '/*This code was auto-generated by calling dbo.USP_CREATEORUPDATE_BUILDNAMEFORMATTVF on ' + cast(getdate() as nvarchar) + '. */' + char(10);
set @SQL = @SQL + 'as' + char(10) + 'return' + char(10) + '(' + nchar(10) + 'select' + char(10);
set @SQL_FROM = 'from dbo.CONSTITUENT with(nolock)' + char(10);
declare @FIELDS table
(
[REMOVETRAILINGSPACE] bit,
[INITIAL] bit,
[SMARTREMOVE] bit,
[ID] uniqueidentifier,
[FIELDCODE] tinyint,
[SPECIFIEDTABLEVALUEFUNCTION] nvarchar(100),
[INDEX] int,
[FIELDNAME] nvarchar(200)
)
insert into @FIELDS
select
NAMEFORMATFUNCTIONDETAIL.REMOVETRAILINGSPACE,
NAMEFORMATFUNCTIONDETAIL.INITIAL,
NAMEFORMATFUNCTIONDETAIL.SMARTREMOVE,
NAMEFORMATFIELD.ID,
NAMEFORMATFIELD.FIELDCODE,
NAMEFORMATFIELD.SPECIFIEDTABLEVALUEFUNCTION,
ROW_NUMBER() over (order by NAMEFORMATFUNCTIONDETAIL.SEQUENCE desc),
case NAMEFORMATFIELD.FIELDCODE
when 0 then '''' + replace(NAMEFORMATFIELD.DISPLAY, '''', '''''') + ''''
when 1 then 'CONSTITUENT.KEYNAME'
when 2 then 'CONSTITUENT.FIRSTNAME'
when 3 then 'CONSTITUENT.MIDDLENAME'
when 6 then 'SPOUSE.KEYNAME'
when 7 then 'SPOUSE.FIRSTNAME'
when 8 then 'SPOUSE.MIDDLENAME'
else case when NAMEFORMATFIELD.FIELDCODE = 11 then
case NAMEFORMATFIELD.SPECIFIEDTABLEVALUEFUNCTION
when 'dbo.UFN_CONSTITUENT_GETNICKNAME_TVF' then 'CONSTITUENT.NICKNAME'
when 'dbo.UFN_CONSTITUENT_GETMAIDENNAME_TVF' then 'CONSTITUENT.MAIDENNAME'
when 'dbo.UFN_CONSTITUENT_GETSPOUSENICKNAME_TVF' then 'SPOUSE.NICKNAME'
when 'dbo.UFN_CONSTITUENT_GETSPOUSEMAIDENNAME_TVF' then 'SPOUSE.MAIDENNAME'
else 'F' + cast(ROW_NUMBER() over (order by NAMEFORMATFUNCTIONDETAIL.SEQUENCE desc) as nvarchar(9)) +
case when NAMEFORMATFIELD.SPECIFIEDTABLEVALUEFUNCTION in (
'dbo.UFN_CONSTITUENT_GETTITLE2_TVF',
'dbo.UFN_CONSTITUENT_GETSUFFIX2_TVF',
'dbo.UFN_CONSTITUENT_GETSPOUSETITLE2_TVF',
'dbo.UFN_CONSTITUENT_GETSPOUSESUFFIX2_TVF'
)
then '.DESCRIPTION'
else '.VALUE'
end
end
else 'F' + cast(ROW_NUMBER() over (order by NAMEFORMATFUNCTIONDETAIL.SEQUENCE desc) as nvarchar(9)) + '.DESCRIPTION'
end
end
from dbo.NAMEFORMATFUNCTIONDETAIL
inner join dbo.NAMEFORMATFIELD on NAMEFORMATFUNCTIONDETAIL.NAMEFORMATFIELDID = NAMEFORMATFIELD.ID
where
NAMEFORMATFUNCTIONDETAIL.NAMEFORMATFUNCTIONID = @ID
order by
NAMEFORMATFUNCTIONDETAIL.SEQUENCE desc;
declare @FIELDCOUNT int = @@ROWCOUNT
declare @ROW int = 1
declare @REMOVETRAILINGSPACE bit;
declare @INITIAL bit;
declare @SMARTREMOVE bit;
declare @NAMEFORMATFIELDID uniqueidentifier;
declare @FIELDCODE int;
declare @SPECIFIEDTABLEVALUEFUNCTION nvarchar(100);
declare @FIELD_TABLEALIAS nvarchar(10) = null;
declare @FIELD_FROM nvarchar(max); -- Used when including additional tables for field.
declare @FIELDNAME nvarchar(200) = null;
declare @PREVIOUSFIELDNAME nvarchar(200);
declare @NEXTFIELDNAME nvarchar(200);
declare @INCLUDESPOUSE bit = 0;
-- Note we walk reverse to simplify handling of spacing and smart remove
while @ROW <= @FIELDCOUNT begin
select
@REMOVETRAILINGSPACE = REMOVETRAILINGSPACE,
@INITIAL = INITIAL,
@SMARTREMOVE = SMARTREMOVE,
@NAMEFORMATFIELDID = ID,
@FIELDCODE = FIELDCODE,
@SPECIFIEDTABLEVALUEFUNCTION = SPECIFIEDTABLEVALUEFUNCTION,
@FIELD_FROM = '',
@FIELD_TABLEALIAS = 'F' + cast(@ROW as nvarchar(9)),
@FIELDNAME = FIELDNAME,
@PREVIOUSFIELDNAME =
case when SMARTREMOVE != 0
then (select FIELDNAME from @FIELDS where @ROW - 1 = [INDEX] and FIELDCODE != 0)
else null
end,
@NEXTFIELDNAME =
case when SMARTREMOVE != 0
then (select FIELDNAME from @FIELDS where @ROW + 1 = [INDEX] and FIELDCODE != 0)
else null
end
from @FIELDS
where @ROW = [INDEX]
if @FIELDCODE = 4
begin
set @FIELD_FROM = ' left outer join dbo.TITLECODE ' + @FIELD_TABLEALIAS + ' with(nolock) on ' + @FIELD_TABLEALIAS +'.ID = CONSTITUENT.TITLECODEID' + char(10);
end
else if @FIELDCODE = 5
begin
set @FIELD_FROM = ' left outer join dbo.SUFFIXCODE ' + @FIELD_TABLEALIAS + ' with(nolock) on ' + @FIELD_TABLEALIAS +'.ID = CONSTITUENT.SUFFIXCODEID' + char(10);
end
else if @FIELDCODE = 9
begin
set @FIELD_FROM = ' left outer join dbo.TITLECODE ' + @FIELD_TABLEALIAS + ' with(nolock) on ' + @FIELD_TABLEALIAS +'.ID = SPOUSE.TITLECODEID' + char(10);
end
else if @FIELDCODE = 10
begin
set @FIELD_FROM = ' left outer join dbo.SUFFIXCODE ' + @FIELD_TABLEALIAS + ' with(nolock) on ' + @FIELD_TABLEALIAS +'.ID = SPOUSE.SUFFIXCODEID' + char(10);
end
else if @FIELDCODE = 11
begin
if @SPECIFIEDTABLEVALUEFUNCTION is null or @SPECIFIEDTABLEVALUEFUNCTION = ''
begin
raiserror('ERR_NAMEFORMAT_SPECIFIEDTABLEVALUEFUNCTION_MISSING', 13, 1);
return -1
end
-- If this field is one of the standard misidentified constituent fields, then optimize the SQL.
-- Since there is no good way to identify the fields, look for the UFNs we replace
if @SPECIFIEDTABLEVALUEFUNCTION = 'dbo.UFN_CONSTITUENT_GETTITLE2_TVF'
begin
set @FIELD_FROM = ' left outer join dbo.TITLECODE ' + @FIELD_TABLEALIAS + ' with(nolock) on ' + @FIELD_TABLEALIAS +'.ID = CONSTITUENT.TITLE2CODEID' + char(10);
end
else if @SPECIFIEDTABLEVALUEFUNCTION = 'dbo.UFN_CONSTITUENT_GETSUFFIX2_TVF'
begin
set @FIELD_FROM = ' left outer join dbo.SUFFIXCODE ' + @FIELD_TABLEALIAS + ' with(nolock) on ' + @FIELD_TABLEALIAS +'.ID = CONSTITUENT.SUFFIX2CODEID' + char(10);
end
else if @SPECIFIEDTABLEVALUEFUNCTION = 'dbo.UFN_CONSTITUENT_GETSPOUSETITLE2_TVF'
begin
set @INCLUDESPOUSE = 1;
set @FIELD_FROM = ' left outer join dbo.TITLECODE ' + @FIELD_TABLEALIAS + ' with(nolock) on ' + @FIELD_TABLEALIAS +'.ID = SPOUSE.TITLE2CODEID' + char(10);
end
else if @SPECIFIEDTABLEVALUEFUNCTION = 'dbo.UFN_CONSTITUENT_GETSPOUSESUFFIX2_TVF'
begin
set @INCLUDESPOUSE = 1;
set @FIELD_FROM = ' left outer join dbo.SUFFIXCODE ' + @FIELD_TABLEALIAS + ' with(nolock) on ' + @FIELD_TABLEALIAS +'.ID = SPOUSE.SUFFIX2CODEID' + char(10);
end
else if @SPECIFIEDTABLEVALUEFUNCTION not in (
'dbo.UFN_CONSTITUENT_GETNICKNAME_TVF',
'dbo.UFN_CONSTITUENT_GETMAIDENNAME_TVF',
'dbo.UFN_CONSTITUENT_GETSPOUSENICKNAME_TVF',
'dbo.UFN_CONSTITUENT_GETSPOUSEMAIDENNAME_TVF'
)
begin
set @FIELD_FROM = ' outer apply ' + @SPECIFIEDTABLEVALUEFUNCTION + '(CONSTITUENT.ID) ' + @FIELD_TABLEALIAS + char(10);
end
end
else if @FIELDCODE > 11
begin
raiserror('ERR_NAMEFORMAT_UNSUPPORTEDFIELDCODE', 13, 1);
return -1
end
set @SQL_SELECT = ' ' +
-- If this isn't a constant, check for null and empty
case when @FIELDCODE != 0
then
'case when len(coalesce(' + @FIELDNAME + ', '''')) = 0' +
-- Account for smart remove, if next field is blank, don't include this one.
case when @SMARTREMOVE != 0 and @PREVIOUSFIELDNAME is not null
then ' or len(coalesce(' + @PREVIOUSFIELDNAME + ', '''')) = 0'
else ''
end +
case when @SMARTREMOVE != 0 and @NEXTFIELDNAME is not null
then ' or len(coalesce(' + @NEXTFIELDNAME + ', '''')) = 0'
else ''
end + ' then '''' else '
else case when @SMARTREMOVE != 0 and (@PREVIOUSFIELDNAME is not null or @NEXTFIELDNAME is not null)
then 'case when ' +
case when @PREVIOUSFIELDNAME is not null
then 'len(coalesce(' + @PREVIOUSFIELDNAME + ', '''')) = 0'
else ''
end +
case when (@PREVIOUSFIELDNAME is not null and @NEXTFIELDNAME is not null)
then ' or '
else ''
end +
case when @NEXTFIELDNAME is not null
then 'len(coalesce(' + @NEXTFIELDNAME + ', '''')) = 0'
else ''
end + ' then '''' else '
else ''
end
end +
-- DO NOT CALL dbo.UFN_MAKEINITIALS,
-- unrolling and simplifying the UFN is extremely important to performance
case when @INITIAL != 0
then
'left (' + @FIELDNAME + ', 1) + ''.'''
else @FIELDNAME
end +
-- Append spacer
case when (@ROW > 1 and @REMOVETRAILINGSPACE = 0)
then ' + '' '''
else ''
end +
-- If this isn't a constant, close the clause
case when (@FIELDCODE != 0 or (@SMARTREMOVE != 0 and (@PREVIOUSFIELDNAME is not null or @NEXTFIELDNAME is not null)))
then
' end'
else ''
end +
-- Append concatenation logic
case when @ROW > 1
then ' + '
else ''
end + char(10) + @SQL_SELECT;
-- Depending on the field type add field's table to the from clause
if (@INCLUDESPOUSE != 0 or @FIELDCODE in (6,7,8,9,10)) and @SPOUSETABLE_INCLUDED = 0
begin
set @SPOUSETABLE_INCLUDED = 1;
set @SQL_FROM = @SQL_FROM + ' left outer join dbo.RELATIONSHIP with(nolock) on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID and RELATIONSHIP.ISSPOUSE = 1' + char(10);
set @SQL_FROM = @SQL_FROM + ' left outer join dbo.DECEASEDCONSTITUENT with(nolock) on DECEASEDCONSTITUENT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID' + char(10);
set @SQL_FROM = @SQL_FROM + ' left outer join dbo.CONSTITUENT as SPOUSE with(nolock) on SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID and SPOUSE.ISINACTIVE = 0 and DECEASEDCONSTITUENT.ID is null' + char(10);
end
set @SQL_FROM = @SQL_FROM + @FIELD_FROM;
set @ROW = @ROW + 1;
end
-- If the name format was not defined by the user, no meta data exists to build the TVF dynamically
if (@FIELD_TABLEALIAS is null)
begin
raiserror('ERR_NAMEFORMAT_FUNCTIONDETAILS_MISSING', 13, 1);
return -1;
end
set @SQL = @SQL + ' rtrim(ltrim(' + char(10) + @SQL_SELECT + ')) as NAME' + char(10) + @SQL_FROM;
set @SQL = @SQL + 'where' + char(10);
set @SQL = @SQL + ' (@CONSTITUENTID is null or @CONSTITUENTID = dbo.CONSTITUENT.ID)' + char(10);
set @SQL = @SQL + ')' + char(10);
exec (@SQL);
end try
begin catch
-- Suppress errors, clear the TVF field.
set @NAMEFORMAT_TVF = '';
end catch
update dbo.NAMEFORMATFUNCTION set
FORMATSQLTABLEVALUEFUNCTION = @NAMEFORMAT_TVF,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where ID = @ID;
return 0