Slate provides several functions defined in the SQL server database that may be used as macros to make SQL code simpler and cleaner. The following is a partial listing of some commonly used 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.
Important!
Although table-valued functions perform well when exporting data, they are not suitable for use in a WHERE clause against a table with many rows. It is dramatically less efficient to use a function in a WHERE clause (where the function is essentially a subquery) than to use a clause such as "where (p.[id] in (select [record] from [field] where ([field] = 'term') and ([prompt] in (select [id] from [lookup.prompt] where ([key] = 'term') and ([value] in ('2013 Fall', '2014 Fall'))))))".
- (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
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.
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.