Slate user-defined SQL functions

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.

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.

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
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.
Was this article helpful?
21 out of 24 found this helpful