Table-valued functions

These functions return a set of table rows.  In most cases, only a single row and value are returned, as is demonstrated in the form of the subqueries below.  Table-valued functions are used in favor of scalar functions, as SQL Server will expand table-valued functions when designing the execution plan, whereas with scalar functions it will execute them as part of a nested loop.  When used with queries that return many records or need to evaluate the result of the function against many records, table-valued functions significantly outperform their scalar counterparts.

• (select [value] from dbo.getFieldTopTable(record, field))
  
  Returns the prompt value or free-text value for a field for a given record.  If executed for a multi-valued field, it will return the entry from the field table with the highest order.  If no order has been specified, the returned value may be arbitrary.
  
  record parameter:  This takes in the GUID of the related record, such as the [id] from the [person] or [application] table.
  
  field parameter:  This takes in a string (such as 'term') that corresponds to the [key] in [lookup.prompt] 

• (select [value] from dbo.getFieldTable(record, field))
  
  Returns the prompt value or free-text value for a field for a given record.  If executed for a multi-valued field, it will return a comma-separated list of the values.
  
  record parameter:  This takes in the GUID of the related record, such as the [id] from the [person] or [application] table.
  
  field parameter:  This takes in a string (such as 'interest') that corresponds to the [key] in [lookup.prompt]

• (select [value] from dbo.getFieldExportTable(record, field))
  
  Returns the export value for a prompt-based field for a given record.  If executed for a multi-valued field, it will return the entry from the field table with the highest order.  If no order has been specified, the returned value may be arbitrary.
  
  record parameter:  This takes in the GUID of the related record, such as the [id] from the [person] or [application] table.
  
  field parameter:  This takes in a string (such as 'term') that corresponds to the [key] in [lookup.prompt]

• (select [value] from dbo.getPromptTable(prompt))
  
  Returns the value for a prompt given a prompt GUID.
  
  prompt parameter:  This takes in the GUID of a prompt (the [id] from [lookup.prompt]) as it may be specified on tables such as the [degree] column on the [school] table

• (select [value] from dbo.getPromptExportTable(prompt))
  
  Returns the export value for a prompt given a prompt GUID.
  
  prompt parameter:  This takes in the GUID of a prompt (the [id] from [lookup.prompt]) as it may be specified on tables such as the [degree] column on the [school] table

Scalar functions (deterministic)

These functions return a static value given a particular set of inputs.

• dbo.convertScore(src, dst, score)
  
  Returns a converted or scaled test score with type 'dst' from an original 'score' of the type 'src'.  The following values for 'src' and 'dst' are accepted:
  'ACT'  - ACT
  'SAT' - SAT on 1600 scale
  'SAT2400' - SAT on 2400 scale
  
  The function currently only converts between ACT and SAT, ACT and SAT2400, and their inverses.

• dbo.md5(input)
  
  Returns a uniqueidentifier of an MD5 hash given an 'input' of varbinary(max).

• dbo.isValidEmail(input)
  
  Returns a bit from an input varchar(max) where the return value is 1 if the input is a validly-formatted email address and 0 if not.

• dbo.getToken(separator, value, index)
  
  Tokenizes a 'value' input by splitting the 'value' using the 'separator' string and returning the value at a one-based 'index' from the split array.

• dbo.getDate(input)
  
  Returns a date-typed value given a varchar 'input' in a variety of date formats.

• dbo.toProperCase(input)
  
  Returns a proper-cased value given 'input'.  If 'input' already contained mixed-case, the 'input' will be echoed back.  Certain recognizable strings, such as 'PO' (as in 'PO Box') and cardinal directions (SSW, NNE) will retain their uppercasing.

Scalar functions (nondeterministic)

These functions return a nondeterministic value given a set of inputs.

• dbo.getAge(birthdate)
  
  Returns the age in years given an input parameter 'birthdate'.

• dbo.generatePin()
  
  Returns a 9-digit random PIN.