Using functions in custom procs

Several functions are included for use the the core procedures. This functions are also handy for custom procedures. using special functions in procedures

Table based functions

ParseDelimitedString

DECLARE @String NVARCHAR(1000)
SET @string = 'item1, item 2, item 3'
select * from dbo.fnMFParseDelimitedString(@String,',') as fmpds

-- this function is used is a cross apply to show all the items for a multilookup

SELECT * FROM dbo.MFCustomerOrder AS mco
CROSS APPLY dbo.fnMFParseDelimitedString(OrderLine_ID,',') AS fmpds
INNER JOIN MFOrderLines mol
ON mol.objid = fmpds.listitem

Split This function is specifically designed to parse a paired group of property ids, and there value.

select * from dbo.fnMFSplit('0,1079','Name of object, Customer',',') as fms

SplitPairedString This function is similar to Split, however it allows for including multi lookups as a inner delimited string

select * from dbo.fnMFSplitPairedStrings('Customer_ID,Project_ID,Objid','12;36;78,7,10007',',',';') as fmsps

SplitString this is similar to ParseDelimitedString

select * from dbo.fnMFSplitString('4,5,6,7,8,9,0',',') as fmss

Scalar Functions

The following functions could be used in some cases in custom procedures and is widely used by MFSQL procedures in internal operations.

The following functions are discussed elsewhere

select dbo.fnMFCapitalizeFirstLetter('this is my name') -- mainly used to normalise auto generation of class table names

select dbo.fnMFReplaceSpecialCharacter('t%his ,i*llus!trate it') -- note that the removal of special characters is focussed on removing characters that could interfere with the connector operations

select dbo.fnMFVaultSettings() -- use to generate the vault settings parameter for CLR procedures

select dbo.fnMFVariableTableName('MFTable',replace(convert(varchar(15), getdate(),110),'-','')) widely used to generate dynamic naming of temporary files in procedures

select dbo.fnMFTextToDate('2023-01-01') -- use to convert text to date format

select dbo.fnMFLastConnectorVersion() -- use to get the last connector version

select dbo.fnMFRemoveIllegalXmlCharacters('<test>Some invalid characters: & % $ #</test>') -- use to remove illegal XML characters

Select dbo.fnMFFloatToString(123.45, 2) -- use to convert float to string dynamically as part of a process as a decimal text value