--- title: "Slate user-defined SQL functions" slug: "slate-user-defined-sql-functions" updated: 2026-04-16T19:19:33Z published: 2026-04-16T19:19:33Z --- > ## Documentation Index > Fetch the complete documentation index at: https://knowledge.technolutions.net/llms.txt > Use this file to discover all available pages before exploring further. # 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. > [!CAUTION] > 🔔 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.