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
fnMFMultiLookupUpsert — example: Using multilookup upsert
fnMFObjectHyperlink — overview and examples: Using hyperlinks with MFSQL Connector
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